How to clean messy data in Excel: 9 fixes

Exports from CRMs, web forms, accounting tools and PDFs rarely arrive ready to analyze. Names have stray spaces, numbers won't add up, dates won't sort, and "NY", "N.Y." and "New York" all count as different states. Here are nine fixes in the order we apply them on client spreadsheets, plus the checks that prove nothing was lost along the way. All formulas were tested, and they work in Excel 2019, Excel 2021 and Microsoft 365 unless noted otherwise.

1. Work on a copy, as a table

Keep the original export untouched on its own tab, called Raw or similar, and do all your cleaning on a copy. Then click inside the data and press Ctrl+T to make it an Excel table. Tables fill formulas down automatically and keep filters and sorts on the right range. Before you change anything, note the row count shown in the status bar.

2. Remove extra spaces (when TRIM isn't working)

=TRIM(A2) removes leading and trailing spaces and squeezes double spaces down to one. If TRIM seems to do nothing, the culprit is usually a non-breaking space (character 160), which is common in data copied from web pages and PDFs. TRIM doesn't remove it, so swap it for a normal space first:

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

In our test, a cell containing "  Acme<nbsp>Corp  " came out as a clean 9-character "Acme Corp".

3. Strip line breaks and odd characters

=CLEAN(A2) removes non-printing characters, including the line breaks that appear when someone pressed Alt+Enter in a cell. Combine it with the space fix for a general-purpose text cleaner:

=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))

4. Convert numbers stored as text, all at once

Numbers stored as text show up as left-aligned values with green triangles, and SUM ignores them. There are three ways to convert a whole column, depending on what's mixed in with the digits:

• Plain digits: select the column and go to Data > Text to Columns > Finish. Excel re-reads every cell as a number. This is the fastest bulk fix.
• Mixed ranges: type 1 in an empty cell and copy it. Select the text-numbers, then choose Paste Special > Values + Multiply. Delete the 1 afterwards.
• Symbols, commas or trailing minus signs: use a formula. =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) turns "$1,050" into 1050. Our PDF-to-Excel guide has tested formulas for "125-" and "(89.10)" negatives.

Watch for ID numbers such as ZIP codes, part numbers and phone numbers. These should stay as text, or you'll lose leading zeros.

5. Fix dates Excel doesn't recognize

If dates won't sort or group in a pivot table, they're text. The most common cause is a day/month/year export opened on a computer set to month/day/year.

• Text to Columns: select the column, then choose Data > Text to Columns > Delimited > Next > Next. Under Column data format, choose Date and pick the order the data is actually in, such as DMY. Click Finish.
• Formula: for a fixed pattern like 31/12/2025, rebuild the date from its parts: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).

Avoid relying on DATEVALUE for ambiguous dates. It reads text according to your computer's regional settings, so 03/04/2026 could become March 4 or April 3. Once converted, check a few dates where the day is greater than 12, because those can only be read one way.

6. Standardize case and spelling

• =PROPER(A2) turns "JANE DOE" into "Jane Doe", =UPPER() handles state codes, and =LOWER() suits email addresses.
• For category values, filter the column and look at the unique values in the dropdown list. Variants like "NY", "N.Y." and "new york" jump out.
• For many variants, build a small two-column mapping table (Variant and Standard) and look each value up: =XLOOKUP(A2,Map[Variant],Map[Standard],A2). The last argument keeps the original value if there's no match. XLOOKUP needs Excel 2021 or Microsoft 365.

7. Split or combine columns

• Split: use Data > Text to Columns with a comma or space delimiter, or =TEXTSPLIT(A2,",") in Microsoft 365.
• Flash Fill: type the result you want for the first row or two (for example, first names only), then press Ctrl+E. Check the output, because Flash Fill guesses the pattern and can guess wrong on unusual rows.
• Combine: =A2&" "&B2, or =TEXTJOIN(", ",TRUE,A2:C2) to skip blanks.

8. Remove duplicates safely

Deduplicate after the steps above, because trailing spaces and inconsistent case hide duplicates. Excel always keeps the first occurrence, so sort first to control which row survives. Our full guide on removing duplicates but keeping the latest row walks through it.

9. Handle blanks and errors

• Find blanks: select the range and go to Home > Find & Select > Go To Special > Blanks. Decide case by case whether a blank means zero, unknown or "same as the row above". They aren't the same thing.
• Fill down repeated labels: after selecting the blanks, type =, press the up arrow, then press Ctrl+Enter. Every blank copies the cell above it. Convert the results to values afterwards.
• Errors: =IFERROR(formula,"") hides errors, but hiding isn't fixing. Filter for #N/A and #VALUE! first to understand why they happen.

When you're done, select your helper columns and use Copy > Paste Special > Values so the clean data no longer depends on the raw tab.

Verify before you hand it over

1. Row count: the same as the raw tab, minus only the duplicates you removed on purpose.
2. Totals: =SUM() key number columns on both tabs. They should match, adjusted for any removed duplicates.
3. Type check: =COUNT(C2:C1000) (numbers only) should equal =COUNTA(C2:C1000) for a number column. A gap means some cells are still text.
4. Dates: sort oldest to newest and confirm the first and last dates make sense.

Keep a short note of what you changed: "Trimmed names, converted 2 date columns from DMY, removed 37 duplicate emails (kept latest)." That note is what makes cleaned data trustworthy to the next person who opens it. We send one with every cleanup job, and our CSV cleanup sample shows one.

FAQ