Spreadsheet Data Cleaning

Spreadsheet Data Cleaning

When to Use

Use this skill when:

• The user has a dataset open in Excel or Google Sheets and describes specific data quality problems -- extra spaces, inconsistent casing, phone numbers in multiple formats, dates stored as text, currency fields with symbols embedded, or columns with mixed types
• The user asks a formula-level question such as "how do I strip the dollar sign from this column," "how do I find all the duplicate emails," or "how do I fill in the blanks with the value from the row above"
• The user needs a repeatable, auditable cleaning workflow that lives inside the spreadsheet itself -- no Python, no Power Query, no SQL -- just formulas dragged down a column
• The user is preparing data for a pivot table, VLOOKUP/XLOOKUP, dashboard, or chart and the joins or aggregations are failing because of formatting mismatches
• The user has received a data export (from a CRM, ERP, survey tool, or database) and needs to normalize it before analysis
• The user needs a non-destructive cleaning approach where the original data is preserved and cleaned versions live in helper columns for verification
• The user is working with a shared workbook and cannot run external scripts -- all transformations must be formula-based and reviewable by non-technical stakeholders

Do NOT use when:

• The user wants a tool-agnostic data cleaning protocol covering Python pandas, SQL, R, and spreadsheets -- use data-cleaning-protocol instead, which handles cross-tool strategy
• The user wants to set up data validation rules (dropdown lists, input masks, range checks) to prevent dirty data from entering the spreadsheet in the future -- use data-validation-setup
• The user wants to audit formula integrity, circular references, or dependency chains in a financial or analytical model -- use spreadsheet-model-audit
• The user wants to write a Python script using pandas, OpenRefine, or a SQL-based cleaning pipeline -- this is outside the scope of spreadsheet-native formulas
• The user is working with datasets larger than ~500,000 rows in Excel -- at that scale, spreadsheet formulas become unreliable and Power Query or Python is more appropriate; redirect to data-cleaning-protocol
• The user wants to set up macros or VBA to automate cleaning -- this skill is strictly formula-based and spreadsheet-portable
• The user is asking about data modeling, schema normalization, or database design -- those are structural concerns, not formula-level cleaning concerns

Process

Step 1: Diagnose the Data Quality Issues Before Writing a Single Formula

Before prescribing formulas, gather enough information to understand the shape of the problem. Ask the user for:

• Column inventory: What are the column names, data types (text, number, date, boolean), and approximate row count? A column described as "customer ID" behaves differently from "product description."
• Sample values: Request 3-5 representative dirty values per problem column. Do not assume what "inconsistent formatting" means -- "555-1234," "(555) 123-4567," and "5551234" are three different phone problems requiring different formulas.
• Source system: Was this exported from Salesforce, a MySQL dump, a survey platform, or typed manually? Source systems have predictable error patterns (e.g., Salesforce exports often have trailing spaces in lookup fields; Google Forms responses often have inconsistent casing).
• Cleaning intent: Does the user need exact matching (joining on customer ID), display formatting (printing a report), or analysis-readiness (summing a revenue column)?
• Destructive vs. non-destructive: Should cleaning happen in-place (overwriting original values after paste-as-values) or in new columns that run in parallel? Default to non-destructive unless the user explicitly says otherwise.
• Platform: Excel (which version -- 365, 2019, 2016?) or Google Sheets? XLOOKUP, LET, and BYROW are available in Excel 365 and Google Sheets but not in Excel 2016. ARRAYFORMULA is Google Sheets-only.

Run a quick audit with these diagnostic formulas before building the cleaning sequence:

=COUNTBLANK(A2:A1001)          -- count empty cells in a 1000-row column
=SUMPRODUCT(--ISERROR(A2:A1001))  -- count error values (any type)
=SUMPRODUCT(--(LEN(A2:A1001)<>LEN(TRIM(A2:A1001))))  -- count cells with extra spaces
=SUMPRODUCT(--(A2:A1001<>LOWER(A2:A1001)))  -- count cells that are not all lowercase
=SUMPRODUCT(--(ISNUMBER(A2:A1001)*1))  -- count numeric values in a column

These five formulas identify the scope of problems quantitatively before any cleaning begins. Report these counts to the user so they can verify the cleaning worked afterward.

Step 2: Apply Text Cleaning Formulas -- Strip, Normalize, Standardize

Text columns are the most common source of data quality failures. Apply formulas in the order below -- the sequence matters because CLEAN must run before TRIM (CLEAN can create spaces, TRIM removes them), and case normalization should run before SUBSTITUTE (so you match on a known case).

Whitespace and non-printable character removal:

Case standardization:

Targeted replacement:

For known bad values, chain SUBSTITUTE calls:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"N/A",""),"-",""),".",".")))

Build the chain from the innermost substitution outward. Each SUBSTITUTE wraps the previous result. Limit chains to 5-6 levels before recommending a VLOOKUP mapping table approach (see Step 5).

Splitting compound fields:

Step 3: Handle Null Values, Blanks, and Errors -- Explicitly and Intentionally

Blank cells and error values propagate silently through aggregations, lookups, and pivot tables. Every formula that touches a potentially empty or error-prone value must be wrapped.

Blank detection and handling:

=IF(A2="","[MISSING]",A2)               -- flag missing text values visibly
=IF(A2=0,"",(A2))                       -- treat 0 as blank for display (only if 0 is not a valid value)
=IF(ISBLANK(A2),"Unknown",A2)           -- ISBLANK is stricter than ="" (misses formula-generated empty strings)
=IF(LEN(TRIM(A2))=0,"[EMPTY]",TRIM(A2)) -- catches cells that contain only spaces

Error handling:

=IFERROR(VLOOKUP(A2,RefTable,2,FALSE),"Not Found")  -- safe VLOOKUP wrapper
=IFNA(VLOOKUP(A2,RefTable,2,FALSE),"Not Found")     -- only catches #N/A, lets #REF! and #VALUE! surface
=IFERROR(VALUE(A2),A2)                              -- attempt conversion; return original if it fails

Distinguish between IFERROR and IFNA: Use IFNA when you want lookup misses to show "Not Found" but you still want structural errors (#REF!, #DIV/0!) to be visible. Use IFERROR only when you are confident any error should produce the fallback value.

Fill-down (propagate last known value):

=IF(A2="",A1,A2)

This pattern works for hierarchical data (e.g., a "Region" column that only has a value in the first row of each region group). Drag down from row 3 onward. The formula references A1 (the row above), not a fixed cell.

Count-based null audit:

=COUNTBLANK(A2:A1001)       -- total blanks (includes cells with formula returning "")
=COUNTA(A2:A1001)           -- non-blank cells
=ROWS(A2:A1001)-COUNTA(A2:A1001)  -- blank count alternative that excludes formula-empty cells

Step 4: Convert Data Types -- Numbers, Dates, and Currency

Type mismatches are the leading cause of formula failures and incorrect aggregations. A column of numbers stored as text will produce a SUM of zero and fail a VLOOKUP numeric match.

Number conversion:

Detect text-stored numbers (Excel diagnostic):

=ISNUMBER(A2)               -- returns FALSE if number is stored as text
=SUMPRODUCT(--(NOT(ISNUMBER(A2:A1001))*ISNUMBER(VALUE(A2:A1001))))  -- count text-stored numbers in range

Date conversion:

Dates are the most error-prone type conversion because interpretation is locale-dependent. A date exported as "04/05/2024" is April 5 in the US locale and May 4 in the UK locale. Always clarify the source format first.

Target date output format for analysis: Always output dates in ISO 8601 format (YYYY-MM-DD) for downstream compatibility. Use =TEXT(date_formula,"YYYY-MM-DD") to produce a text string, or leave as a date serial and apply cell formatting separately.

Step 5: Detect and Handle Duplicates

Duplicate detection requires a decision about what constitutes a duplicate: exact match, case-insensitive match, or fuzzy match. Spreadsheet formulas handle the first two natively. Fuzzy matching requires a helper approach.

Exact duplicate detection:

=IF(COUNTIF(A$2:A2,A2)>1,"DUPLICATE","FIRST")

The expanding range A$2:A2 is the critical technique: the top boundary is locked ($2), the bottom boundary grows with each row. For row 5, this evaluates A$2:A5. This correctly identifies the first occurrence as FIRST and all subsequent occurrences as DUPLICATE.

Case-insensitive duplicate detection:

COUNTIF in Excel is case-insensitive by default, so COUNTIF(A$2:A2,A2) already treats "Apple" and "apple" as the same value. To force case-sensitive duplicate detection (rare but needed for passwords, codes), use:

=SUMPRODUCT((EXACT(A$2:A2,A2))*1)>1

Multi-column composite key duplicates:

When uniqueness is defined by a combination of columns (e.g., CustomerID + OrderDate must be unique), concatenate them:

=IF(COUNTIF(E$2:E2,E2)>1,"DUPLICATE","")

Where column E contains: =A2&"|"&TEXT(B2,"YYYY-MM-DD") -- the pipe delimiter prevents false collisions where "John" + "Smith" and "Johns" + "mith" would otherwise look identical.

Summary counts:

=COUNTIF(F2:F1001,"DUPLICATE")   -- total duplicate rows (not counting FIRST occurrences)
=SUMPRODUCT((COUNTIF(A2:A1001,A2:A1001)>1)/COUNTIF(A2:A1001,A2:A1001))  -- count of unique values that have at least one duplicate

Removing duplicates non-destructively: Excel's built-in Data > Remove Duplicates is destructive (deletes rows in-place). For non-destructive deduplication, use UNIQUE in Excel 365 or Google Sheets:

=UNIQUE(A2:A1001)       -- returns unique values as a spilled array
=UNIQUE(A2:C1001)       -- unique rows across multiple columns

In Excel 2016 and 2019 (no UNIQUE function), the only non-destructive option is to copy the column to a separate sheet and apply Remove Duplicates there.

Step 6: Handle Large-Scale Text Standardization with Lookup Tables

When a column has more than 10-15 distinct bad values requiring replacement, nested SUBSTITUTE chains become unmanageable. Use a mapping table approach instead.

Build a mapping table:

1. Extract all unique values from the dirty column: copy the column, paste to a helper area, apply Data > Remove Duplicates
2. Add a second column next to the unique values: "Correct Value"
3. Fill in the correct value for each dirty value (leave blanks for values that are already correct)
4. Name the table range CleaningMap (Insert > Name > Define in Excel, or simply use an absolute range reference)

Apply the mapping:

=IFERROR(VLOOKUP(TRIM(LOWER(A2)),CleaningMap,2,FALSE),TRIM(LOWER(A2)))

The IFERROR fallback returns the normalized (trim+lower) original value for anything not found in the mapping table. This means values that are already correct pass through cleanly.

For Excel 365 / Google Sheets, use XLOOKUP:

=IFERROR(XLOOKUP(TRIM(LOWER(A2)),CleaningMap[Raw],CleaningMap[Clean]),TRIM(LOWER(A2)))

XLOOKUP is preferred because it does not require the lookup column to be the leftmost column in the range.

Step 7: Build the Complete Cleaning Sequence -- Order Matters

The sequence in which cleaning operations are applied determines whether they succeed or fail. Run operations in this order:

1. CLEAN then TRIM -- remove non-printable characters first (CLEAN can introduce spaces), then remove spaces. =TRIM(CLEAN(A2)) is the correct nesting order.
2. CHAR(160) substitution -- before TRIM, handle non-breaking spaces that TRIM ignores: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
3. Case normalization -- after whitespace removal, so the case function operates on clean input
4. SUBSTITUTE for known bad strings -- after case normalization, so you can match on a predictable case
5. Type conversion (VALUE, DATEVALUE, DATE) -- after all text cleaning, so the string being converted is clean
6. Blank/error handling (IF, IFERROR) -- outermost wrapper, catches failures from inner operations
7. Duplicate flagging -- after all cleaning is complete, so duplicates are detected against cleaned values, not raw values
8. Validation audit -- final step: compare diagnostic formula counts from Step 1 against post-cleaning counts

Combined master formula for a text field:

=IFERROR(IF(LEN(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))=0,"[MISSING]",PROPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))),A2)

This single formula handles: non-printable chars, non-breaking spaces, extra whitespace, empty/whitespace-only cells, and title-case normalization -- with an IFERROR fallback to the raw original value if anything fails.

Step 8: Validate -- Prove the Cleaning Worked

Every cleaning session must end with a validation step that compares before-and-after metrics. This is not optional -- silent failures (where a formula returns the wrong value without an error) are common in complex nested formulas.

Run these validation checks against both the original column and the cleaned column:

=COUNTA(A2:A1001)                           -- non-blank count (should be equal or higher after cleaning)
=COUNTBLANK(A2:A1001)                       -- blank count (should be equal or lower after cleaning)
=SUMPRODUCT(--ISERROR(E2:E1001))            -- error count in cleaned column (should be 0)
=SUMPRODUCT(--(LEN(E2:E1001)<>LEN(TRIM(E2:E1001))))  -- remaining extra-space cells (should be 0)
=SUMPRODUCT(--(E2:E1001<>LOWER(E2:E1001)))  -- cells not in expected case (for lowercase-expected columns)
=SUMPRODUCT(--(LEN(E2:E1001)=0)*1)         -- count of empty-string cells (different from COUNTBLANK)

Present these results in a before/after table. If any post-cleaning count is worse than the original (e.g., more blanks after cleaning than before), there is a formula error that must be investigated before proceeding.

Output Format

## Spreadsheet Cleaning Plan

### Diagnostic Summary (Pre-Cleaning)
| Column | Type | Total Rows | Blanks | Errors | Extra Spaces | Duplicates |
|--------|------|-----------|--------|--------|-------------|-----------|
| [Col A name] | [text/number/date] | [N] | [=COUNTBLANK] | [=SUMPRODUCT] | [=SUMPRODUCT LEN check] | [=COUNTIF count] |
| [Col B name] | ... | ... | ... | ... | ... | ... |

**Cleaning mode:** Non-destructive (helper columns) / In-place (paste-values after verification)
**Platform:** Excel 365 / Excel 2019 / Google Sheets

---

### Cleaning Operations

#### Operation [N]: [Column Name] -- [Problem Description]
**Source column:** [Letter or name]
**Output column:** [Letter or name -- new helper column]
**Formula (paste into [cell reference], drag down [N] rows):**

=[exact formula string]

**Sequence rationale:** [Why functions are ordered this way -- e.g., "CLEAN before TRIM because CLEAN can produce spaces"]
**Expected transformation:**
- Before: `[sample dirty value 1]` -- After: `[expected clean value 1]`
- Before: `[sample dirty value 2]` -- After: `[expected clean value 2]`
- Before: `[blank / error case]` -- After: `[fallback value]`

---

### Duplicate Detection
**Scope:** [Column(s) defining uniqueness]
**Key construction formula (if multi-column):**

=[concatenation formula for composite key column]

**Duplicate flag formula:**

=IF(COUNTIF([col]$2:[col]2,[col]2)>1,"DUPLICATE","FIRST")

**Summary count:** `=COUNTIF([flag_col]2:[flag_col][last_row],"DUPLICATE")`
**Action required:** [Delete DUPLICATE rows / Review manually / Keep all and flag]

---

### Validation (Post-Cleaning)
| Check | Original Formula | Original Value | Cleaned Formula | Cleaned Value | Pass? |
|-------|-----------------|---------------|----------------|--------------|-------|
| Non-blank count | `=COUNTA([orig])` | [N] | `=COUNTA([clean])` | [N] | ✓ / ✗ |
| Blank count | `=COUNTBLANK([orig])` | [N] | `=COUNTBLANK([clean])` | [N] | ✓ / ✗ |
| Error count | `=SUMPRODUCT(--ISERROR([orig]))` | [N] | `=SUMPRODUCT(--ISERROR([clean]))` | 0 | ✓ / ✗ |
| Remaining spaces | `=SUMPRODUCT(--(LEN([orig])<>LEN(TRIM([orig]))))` | [N] | [same for cleaned] | 0 | ✓ / ✗ |
| Type consistency | `=SUMPRODUCT(--(NOT(ISNUMBER([col]))))` | [N] | [same for cleaned] | 0 | ✓ / ✗ |

---

### Finalization Instructions
1. Review the cleaned columns for unexpected values before overwriting originals
2. Select the cleaned column range (e.g., E2:E1001)
3. Copy (Ctrl+C / Cmd+C)
4. Select the target column (e.g., B2)
5. Paste Special > Values Only (Ctrl+Shift+V in Google Sheets; Alt+E+S+V in Excel)
6. Delete the helper columns after verification
7. Save a backup copy of the original file before any paste-values operation

Rules

1. Always write the exact, paste-ready formula -- never describe intent without formula. If you say "use TRIM to remove spaces," the formula =TRIM(A2) must appear immediately. The user should be able to copy the formula directly from your response.

2. Always recommend non-destructive cleaning (new helper columns) unless the user explicitly requests in-place modification. Paste-as-values should be the final step after the user has verified results, never the first.

3. Nest cleaning functions inside out -- innermost runs first. In =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))), the execution order is: SUBSTITUTE first, then CLEAN, then TRIM. Always explain this order so the user understands why the formula is structured this way.

4. Use the expanding-range duplicate pattern COUNTIF(A$2:A2,A2) -- never COUNTIF(A:A,A2). The full-column version A:A marks every occurrence as a duplicate including the first one, making it impossible to identify which row to keep. The expanding range correctly marks only the second and subsequent occurrences.

5. Use IFNA instead of IFERROR for VLOOKUP and XLOOKUP unless you have a specific reason to suppress all errors. IFERROR silently hides #REF! errors (broken column references) and #VALUE! errors (wrong argument types), which are structural bugs, not data misses. IFNA catches only #N/A (lookup miss), which is the intended case.

6. CLEAN does not remove CHAR(160) (non-breaking space) -- always add a SUBSTITUTE before TRIM for text that originated in web exports, HTML, or Word documents. Forgetting this is the single most common reason TRIM appears to fail. The formula =TRIM(CLEAN(A2)) will leave non-breaking spaces intact; =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) will not.

7. For date columns, always ask the source format before writing a date conversion formula. =DATEVALUE(A2) relies on system locale and will silently produce wrong dates for DD/MM/YYYY values on a US-locale machine. Explicit positional parsing with =DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2)) is locale-independent and always safer.

8. Always include a numeric validation step that compares COUNTBLANK and SUMPRODUCT(--ISERROR()) before and after cleaning. A cleaning formula that silently converts 200 values to blanks or errors has made the data worse, not better, and the user cannot detect this without explicit before/after counts.

9. When nesting more than 5 SUBSTITUTE calls, switch to a VLOOKUP/XLOOKUP mapping table instead. Deeply nested SUBSTITUTE chains are unmaintainable, hard to debug, and produce cryptic error messages when a nested value is wrong. A mapping table is auditable, editable, and scales to hundreds of substitutions.

10. Never suggest PROPER() as a final name-cleaning formula without warning the user about its limitations. PROPER incorrectly capitalizes particles ("Of", "The", "De", "Van"), hyphenated names ("Mary-anne" becomes "Mary-Anne" -- actually correct -- but "McDonald" becomes "Mcdonald"), and initializations ("II" becomes "Ii"). Flag this and provide the workaround of using a correction mapping table for known exceptions.

11. In Google Sheets, ARRAYFORMULA can apply a cleaning formula to an entire column without dragging. =ARRAYFORMULA(TRIM(CLEAN(B2:B1001))) fills the entire range in one formula. However, ARRAYFORMULA does not work with every function (notably IFERROR wrapping can behave differently) -- always test with a 5-row sample before deploying.

12. Always qualify whether a formula produces a text string or a number/date, and make the distinction explicit. =TEXT(A2,"YYYY-MM-DD") produces a text string that looks like a date -- it cannot be used in DATEDIF or subtraction. =DATEVALUE(A2) produces a date serial number that can be used in calculations but must be formatted as a date for display. Confusing these two is a common source of downstream errors.

Edge Cases

1. Numbers Stored as Text (The Green Triangle Problem)

Excel marks number-stored-as-text cells with a small green triangle in the top-left corner. VALUE(A2) alone will fail if the text contains any non-numeric characters (spaces, commas, currency symbols, percent signs).

Diagnostic: =ISNUMBER(A2) returns FALSE for these cells.

Full-coverage formula:

=IFERROR(VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""),"%","")," ",""))),A2)

Strip the four most common non-numeric characters (dollar sign, comma, percent sign, space), TRIM the result, then convert. The outer IFERROR returns the raw original value if conversion still fails -- do not silently return 0, because 0 is a valid data value and masking it creates incorrect aggregations.

After applying this formula, run =SUMPRODUCT(--(NOT(ISNUMBER(E2:E1001)))) on the cleaned column to confirm all values are now truly numeric.

2. Dates in Ambiguous Mixed Formats Within the Same Column

A column containing both "03/04/2025" and "25/03/2025" is not uniformly parseable. The first value could be March 4 (MM/DD) or April 3 (DD/MM). The second is unambiguously DD/MM because no month goes to 25. Mixed-format columns are a data governance problem, not a formula problem.

Handling strategy:

1. Flag rows where both day and month are <= 12 as "AMBIGUOUS": =IF(AND(VALUE(LEFT(A2,2))<=12,VALUE(MID(A2,4,2))<=12),"AMBIGUOUS","UNAMBIGUOUS")
2. Flag rows where the first number is > 12 as definitively DD/MM: =IF(VALUE(LEFT(A2,2))>12,"DD/MM_CONFIRMED","CHECK")
3. Present the count of ambiguous rows to the user and require manual resolution -- do not auto-correct dates you cannot reliably identify
4. After the user resolves ambiguous rows, apply the explicit positional DATE formula to the full column

Rule: Never use DATEVALUE() on a column with known mixed formats. It will silently pick one interpretation based on locale and produce a column of half-correct dates.

3. Unicode Characters, Smart Quotes, and Characters from Copy-Paste

Text copied from Microsoft Word, PDF readers, web browsers, and email clients frequently contains Unicode characters that look like standard ASCII but are not: curly quotes (CHAR 8216, 8217, 8220, 8221), em dashes (CHAR 8212), en dashes (CHAR 8211), ellipsis (CHAR 8230), and non-breaking spaces (CHAR 160). CLEAN removes only CHAR 0-31 and CHAR 127. All of these Unicode characters survive CLEAN untouched.

Comprehensive Unicode cleaning formula:

=TRIM(CLEAN(
  SUBSTITUTE(SUBSTITUTE(
    SUBSTITUTE(SUBSTITUTE(
      SUBSTITUTE(SUBSTITUTE(
        SUBSTITUTE(A2,
          CHAR(8220),""""),CHAR(8221),""""),
        CHAR(8216),"'"),CHAR(8217),"'"),
      CHAR(8211),"-"),CHAR(8212),"-"),
    CHAR(8230),"..."),
  CHAR(160)," ")))

This handles: left/right double quotes, left/right single quotes/apostrophes, en dash, em dash, ellipsis, and non-breaking space -- the seven most common Unicode intrusions in business text data.

Detection formula to identify affected rows:

=SUMPRODUCT(--(LEN(A2:A1001)>LEN(SUBSTITUTE(SUBSTITUTE(A2:A1001,CHAR(8220),""),CHAR(8217),""))))

4. Cleaning Data Across Multiple Sheets Without Copying

When the source data lives on Sheet2 and the user wants cleaned output on Sheet1 (or a separate "Clean" sheet), cross-sheet formula references work identically:

=TRIM(CLEAN(Sheet2!A2))
=IFERROR(VLOOKUP(TRIM(LOWER(Sheet2!A2)),CleaningMap,2,FALSE),TRIM(LOWER(Sheet2!A2)))
=IF(COUNTIF(Sheet2!A$2:Sheet2!A2,Sheet2!A2)>1,"DUPLICATE","FIRST")

The cleaning sheet serves as the audited, verified output while the source sheet remains completely untouched -- the ideal non-destructive architecture. Name the sheets clearly: "Raw_Data" and "Cleaned_Data." This pattern also makes it easy to re-run cleaning if the source is refreshed.

Google Sheets cross-file reference: If the source is a separate Google Sheets file, use IMPORTRANGE:

=TRIM(CLEAN(IMPORTRANGE("spreadsheet_url","Sheet1!A2")))

Note that IMPORTRANGE imports static snapshots that refresh periodically -- it does not reflect live edits in real time and requires the user to authorize the connection between files.

5. Very Long Text Fields With Truncation Risk in Excel

Excel has a cell content limit of 32,767 characters per cell, but a formula result can display at most 32,767 characters. This is rarely a problem for structured data, but it matters for columns containing long-form text (survey responses, notes, comments). CLEAN and TRIM applied to a 30,000-character cell can cause performance issues in worksheets with thousands of such rows.

Symptom: The worksheet becomes unresponsive when dragging the formula down, or the formula bar shows "#VALUE!" errors unexpectedly.

Mitigation:

• Process in batches of 500-1,000 rows at a time rather than dragging to row 50,000 in one operation
• After each batch, select the cleaned cells, copy, and paste-as-values to freeze results and reduce formula load
• For genuinely large text, consider whether CLEAN/TRIM are actually necessary -- truncated cleaning (clean only the first 200 characters) may be sufficient: =TRIM(CLEAN(LEFT(A2,200)))

6. Cleaning Phone Numbers That Have Extensions, Country Codes, or Impossible Lengths

A phone cleaning formula that strips all non-digit characters will produce a 7-digit local number, a 10-digit national number, an 11-digit number with country code (1-555-123-4567), and a 15-digit international number -- all in the same "cleaned" column. There is no single reformatting formula that handles all of these.

Strategy:

1. Strip to digits only first: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"(",""),")",""),"-","")," ",""),".","")
2. Flag by digit length: =LEN(B2) where B2 is the digit-stripped result
3. Apply format only to 10-digit numbers (US standard): =IF(LEN(B2)=10,"("&LEFT(B2,3)&") "&MID(B2,4,3)&"-"&RIGHT(B2,4),"REVIEW: "&B2)
4. Flag 11-digit numbers (with country code 1): =IF(LEN(B2)=11,"+"&LEFT(B2,1)&" ("&MID(B2,2,3)&") "&MID(B2,5,3)&"-"&RIGHT(B2,4),"REVIEW: "&B2)
5. Send anything not 10 or 11 digits to a "REVIEW" flag so the user handles edge cases manually

Never silently truncate phone numbers. Dropping digits from a phone number produces a working-looking but wrong number.

7. Cleaning a Column Where Some Cells Contain Formulas (Not Raw Values)

If some cells in the source column already contain formulas (e.g., =CONCATENATE(C2," ",D2)), the cleaning formula references the formula result, not the underlying formula. This is usually fine -- TRIM and CLEAN work on any text value regardless of whether it was typed or formula-generated.

Problem case: If a cell in the source column contains a formula that references cells in a row being deleted or reorganized, the cleaning formula in the helper column will also break (producing #REF!). The IFERROR wrapper will catch this, but the resulting "[MISSING]" or "0" will hide the data loss.

Safe approach: Before building helper-column cleaning formulas on a column that contains formulas, convert that column to values first (copy > paste-as-values). This freezes the current results and removes formula dependencies, making the cleaning layer stable.

Example

Input: "I downloaded a vendor contact list from our CRM into Excel. It has about 800 rows. Here are the problems I've noticed:

• The Company column has extra spaces and random mixed casing (some ALL CAPS, some all lowercase, some title case)
• The Email column has some cells that say 'N/A' or are blank, and some have spaces around them. A few are all caps.
• The Revenue column has dollar signs and commas in it so my SUM formula returns 0
• The Country column uses about 12 different spellings for the same 5 countries ('USA', 'US', 'United States', 'U.S.A.', 'United States of America' -- all meaning the same thing)
• I need to check if any companies are duplicated I'm using Excel 365. Keep the original data and clean into new columns."

Output:

Spreadsheet Cleaning Plan

Diagnostic Summary (Pre-Cleaning)

Run these formulas to baseline the current state before any cleaning. Paste each formula into an empty cell and record the result.

Cleaning mode: Non-destructive (cleaned values in new columns F, G, H, I, J) Platform: Excel 365

Cleaning Operations

Operation 1: Company Name (Column B -- Company → Column F -- Company_Clean)

Formula (paste into F2, drag down to F801):

=IFERROR(
  IF(LEN(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))=0,
    "[MISSING COMPANY]",
    PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))
  ),
  B2
)

Sequence rationale:

• SUBSTITUTE(B2,CHAR(160)," ") -- replaces non-breaking spaces that TRIM ignores; common in CRM exports
• CLEAN(...) -- removes any non-printable ASCII characters (line feeds, null chars) from the CRM data
• TRIM(...) -- collapses all remaining extra spaces (leading, trailing, double-internal)
• IF(LEN(...)=0,"[MISSING COMPANY]",...) -- flags cells that contain only whitespace
• PROPER(...) -- converts to Title Case for standardized display
• Outer IFERROR(...,B2) -- if anything fails, return the raw original value rather than an error

Expected transformation:

• Before: " ACME CORP " → After: "Acme Corp"
• Before: "global solutions" → After: "Global Solutions"
• Before: " " (spaces only) → After: "[MISSING COMPANY]"
• Before: "Johnson & johnson" → After: "Johnson & Johnson"

Important note: PROPER will incorrectly capitalize particles. If the company list includes entries like "McKinsey & Company" (which PROPER renders as "Mckinsey & Company") or "AECOM" (rendered as "Aecom"), flag these for manual review after applying the formula. Run =COUNTIF(F2:F801,"Mckinsey*") to spot-check known cases.

Operation 2: Email Address (Column C -- Email → Column G -- Email_Clean)

Formula (paste into G2, drag down to G801):

=IF(
  OR(TRIM(C2)="", UPPER(TRIM(C2))="N/A"),
  "[NO EMAIL]",
  LOWER(TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160)," "))))
)

Sequence rationale:

• TRIM(C2)="" catches blank cells and whitespace-only cells
• UPPER(TRIM(C2))="N/A" catches all casing variants of "N/A", "n/a", "N/a" -- normalized before comparison
• LOWER(TRIM(CLEAN(...))) -- for valid emails: strip non-printable chars, remove spaces, convert to lowercase (email addresses are case-insensitive by RFC 5321 standard)
• No IFERROR needed because IF handles both problem states; if LOWER/TRIM/CLEAN fail on a non-text value, add outer IFERROR

Expected transformation:

• Before: " JOHN.SMITH@ACME.COM " → After: "john.smith@acme.com"
• Before: "N/A" → After: "[NO EMAIL]"
• Before: "n/a" → After: "[NO EMAIL]"
• Before: "" (blank) → After: "[NO EMAIL]"
• Before: "Sarah.Jones@COMPANY.COM" → After: "sarah.jones@company.com"

Post-cleaning email format check (optional):

=IF(ISERROR(FIND("@",G2)),"INVALID FORMAT",IF(ISERROR(FIND(".",G2,FIND("@",G2))),"INVALID FORMAT","OK"))

Paste this into column K (Email_Valid) to flag emails missing @ or a domain dot. This is a basic structural check, not a deliverability check.

Operation 3: Revenue (Column D -- Revenue → Column H -- Revenue_Clean)

Formula (paste into H2, drag down to H801):

=IFERROR(
  IF(TRIM(D2)="", 0,
    VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"$",""),",","")," ","")))
  ),
  "[PARSE ERROR: "&D2&"]"
)

Sequence rationale:

• IF(TRIM(D2)="",0,...) -- blank revenue cells become 0 (adjust to a different fallback if 0 is ambiguous in this dataset -- consider "[MISSING]" instead if you need to distinguish zero-revenue from unknown-revenue)
• SUBSTITUTE(D2,"$","") -- removes dollar sign
• SUBSTITUTE(...,",","") -- removes thousands-separator commas
• SUBSTITUTE(...," ","") -- removes any stray spaces within the number
• VALUE(TRIM(...)) -- converts the cleaned text string to a true numeric value
• IFERROR(...,"[PARSE ERROR: "&D2&"]") -- if VALUE still fails (e.g., the cell contained "TBD" or "N/A"), flags the raw original value rather than silently returning 0

Expected transformation:

• Before: "$1,250,000" → After: 1250000 (numeric, no formatting)
• Before: "$500" → After: 500
• Before: "" (blank) → After: 0
• Before: "TBD" → After: "[PARSE ERROR: TBD]"

Verification: After cleaning, =SUM(H2:H801) should return a non-zero total. Also run:

=COUNTIF(H2:H801,"[PARSE ERROR*")   -- count cells that still failed conversion
=SUMPRODUCT(--(NOT(ISNUMBER(H2:H801))))  -- count non-numeric cells in cleaned column (should equal PARSE ERROR count)

Formatting note: Column H will contain raw numbers (e.g., 1250000). Apply cell formatting (Currency or Number with 2 decimal places) for display -- this does not change the underlying value.

Operation 4: Country (Column E -- Country → Column I -- Country_Clean) -- Mapping Table Approach

There are too many variants (at least 12 described) for a SUBSTITUTE chain. Use a mapping table.

Step 4a -- Build the mapping table in columns M and N (or on a separate "Lookup" sheet):

Paste the following table starting at M1:

Select M1:N20 and name this range CountryMap (Formula tab > Name Manager > New in Excel).

Step 4b -- Cleaning formula (paste into I2, drag down to I801):

=IFERROR(
  XLOOKUP(TRIM(UPPER(E2)), UPPER(CountryMap_Raw), CountryMap_Clean, TRIM(PROPER(E2))),
  TRIM(PROPER(E2))
)

Where CountryMap_Raw is the M column range and CountryMap_Clean is the N column range. The UPPER() normalization on both the lookup value and the lookup array makes the match case-insensitive, so "usa", "USA", and "Usa" all match the "USA" entry in the mapping table.

Alternative (for Excel 2019 without XLOOKUP):

=IFERROR(
  INDEX(N$2:N$20, MATCH(TRIM(UPPER(E2)), UPPER(M$2:M$20), 0)),
  TRIM(PROPER(E2))
)

Expected transformation:

• Before: "USA" → After: "United States"
• Before: "United States of America" → After: "United States"
• Before: "u.s.a." → After: "United States" (UPPER normalization handles this)
• Before