How to Clean Messy Data in Excel: TRIM, CLEAN and the Space You Cannot See

1. What it costs: 865 missing from a regional total

Five of the sixteen orders belong to the North region and they add to 2,495. Two of those five rows arrived with a problem you cannot see: one has a trailing space after "North", and one ends in a non-breaking space, the character a web page or a Word document leaves behind when text is pasted through it.

Nothing on screen is different. Here is what the workbook says.

North revenue, clean data                       2495
North revenue, same data with two bad cells     1630
Rows that are exactly "North" out of five          3
Distinct regions, clean                            4
Distinct regions, messy                            6

865 has left the total, which is 425 plus 440, the two affected orders. And the region count went from four to six, because "North " and "North" plus a non-breaking space are each their own region as far as Excel is concerned.

Say out loud which of those two symptoms you would notice first in a real workbook. The missing 865 is invisible; nothing says a total should have been bigger. The region count is the tell, and it is the reason the first thing to do with any new text column is count its distinct values and ask whether that number is the number you expected.

2. Find it before you fix it: LEN and CODE

Two formulas do the whole diagnosis, and neither of them changes anything.

LEN counts characters, so it turns an invisible problem into a number.

=LEN(D8)      6      you can see five letters
=LEN(D12)     6      you can see five letters

Both are one too long. That is enough to know something is wrong, and not enough to know what to do, because the two cells have different problems and need different fixes. CODE gives the identity of a character as a number, so pointing it at the last character names the culprit.

=CODE(RIGHT(D8,1))      32     an ordinary space
=CODE(RIGHT(D12,1))    160     a non-breaking space

32 is the space bar. 160 is the character produced by   on a web page, and by Word when it is keeping two words on the same line. They look identical in every font. Only one of them is removed by the function everybody reaches for first.

Keep those two formulas as a habit rather than a rescue. =LEN(A2) filled down a column, and =CODE(RIGHT(A2,1)) on the one row that is too long, is a thirty-second diagnosis that turns "the lookup is not working" into "there is a character 160 on row 12".

3. TRIM: what it removes, and what it squashes

Before the explanation: TRIM is described everywhere as removing spaces. Say what you think it does to "Dana Reyes", with three spaces in the middle.

=TRIM("Dana   Reyes")       Dana Reyes
=LEN(TRIM("Dana   Reyes"))          10

It does two jobs, not one. It removes spaces from the start and end completely, and it squashes any run of spaces inside the text down to a single space. Ten characters: nine letters and one space. That second behaviour is usually what you want and is occasionally not, so it is worth knowing rather than discovering.

On the trailing-space cell it works exactly as advertised.

=LEN(D8)            6
=LEN(TRIM(D8))      5

One character gone, and the value now matches every other "North" in the column. If your problem is ordinary spaces, this is the whole fix, and it is why TRIM around a lookup key is the standard first thing to try when a lookup returns #N/A for a value you can see.

4. The space TRIM will not remove

Before the explanation: the other bad cell ends in character 160. Predict what TRIM does to it.

=LEN(D12)              6
=LEN(TRIM(D12))        6
=LEN(CLEAN(D12))       6

Nothing. Neither function touches it. TRIM removes character 32 and only character 32, and CLEAN removes the control characters numbered 0 to 31, which does not include 160 either. This is the single most common reason somebody says "I already trimmed it and it still does not match".

The fix is to convert the character into an ordinary space first, and then trim.

=TRIM(SUBSTITUTE(D12, CHAR(160), " "))

=LEN(  that  )                          5
=  that  = "North"                   TRUE

SUBSTITUTE takes the text, the thing to find and the thing to put in its place, and replaces every occurrence. Nesting it inside TRIM gives one formula that handles both kinds of space at once, which is the version worth keeping.

Make that your default cleaning formula for any text column arriving from outside your workbook:

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

It costs nothing on data that was already clean and saves the afternoon on data that was not.

5. CLEAN, and the two words it welds together

CLEAN removes non-printing control characters, the commonest of which by far is the line break somebody pressed Alt+Enter to make, or that arrived inside a field exported from another system.

One of the rep names in the dirty copy has a line break inside it, so the cell shows the name across two lines. Here is what happens.

=LEN(C10)                    9
=LEN(CLEAN(C10))             8
=CLEAN(C10) = "OwenPark"  TRUE

Read that last line twice. CLEAN deleted the line break rather than replacing it, so "Owen" and "Park" are now welded into one word with no space. The cell no longer looks broken, which is worse than looking broken, because the name is now wrong in a way that survives every subsequent check.

A line break is a separator, so it should become a space, not nothing.

=TRIM(SUBSTITUTE(C10, CHAR(10), " "))       Owen Park

Character 10 is the line feed, and character 13 is the carriage return; text from older systems can carry either or both. If you are cleaning a column that might hold multi-line notes, substitute both, and only reach for CLEAN when you genuinely want the character gone rather than replaced.

6. PROPER, UPPER and LOWER, and the names PROPER gets wrong

Case is the other way two identical values become two different ones. UPPER and LOWER are exactly what they sound like and have no surprises in them. PROPER capitalises the first letter of every word, and it has several.

=PROPER("owen park")      Owen Park       right
=PROPER("o'brien")        O'Brien         right
=PROPER("mcdonald")       Mcdonald        wrong
=PROPER("IBM")            Ibm             wrong

It gets the apostrophe case right, which is genuinely useful. It cannot know that McDonald has a capital in the middle, and it will happily destroy an acronym that was already correct, because it applies one rule to every word it meets.

So the rule for names and codes: never run PROPER over a column of real names and consider it cleaned. Use it on data you know is all one case and all ordinary words, check the output by eye, and keep the original column so the damage is reversible. For matching purposes you rarely need pretty capitals anyway; comparisons in Excel ignore case already, so UPPER on both sides is a safer way to force agreement than trying to make each value look nice.

Picture your own worst column for a second: customer names, supplier names, product codes typed by four different people. How many of those entries would PROPER quietly damage, and would anybody notice before the mailing went out?

7. Numbers stored as text

The same problem in the numeric columns, with a different symptom. One of the sixteen orders has its Units value entered as text rather than as a number. Nothing shows except a small green triangle in the corner, which most people have learned to ignore.

=SUM(clean Units)      101
=SUM(messy Units)       95
=COUNT(messy Units)     15
=ISTEXT(that cell)    TRUE

SUM skips text, so the total is short by exactly the value it skipped, and COUNT says 15 where there are 16 rows. That COUNT is the cheapest detector there is: put =COUNT(range) next to any numeric column and compare it with the row count. A mismatch is text pretending to be a number.

Three ways to fix it, in order of how much you should like them. Best, fix the import so it never arrives as text, which for a CSV means declaring the column types in the import dialog rather than double-clicking the file; the import dialog that deletes your zip codes covers the same dialog from the opposite direction. Next best, convert in a new column with =VALUE(A2), which returns a real number, or multiply by 1, which also works. Last resort, select the column, click the warning triangle and choose Convert to Number, which edits the data in place and leaves no record that it happened.

One warning about VALUE: it fails with #VALUE! on anything that is not a clean number, including a value with a stray space or a currency symbol. That failure is useful, because it points at the rows that need a human. Wrapping it to hide the failure would put you back where hiding an error always puts you.

8. Clean in a new column, then prove it worked

Two working rules matter more than any of the functions.

Never clean in place. Put the cleaning formula in a new column beside the original, called Region_clean or similar, and point your analysis at that. Overwriting the raw column destroys the only evidence of what arrived, so when the numbers disagree with the source system next month there is nothing to compare against. It also makes the cleaning repeatable: next month's file drops in and the column recalculates.

Prove it, do not assume it. Cleaning is the one activity where the work and the check look identical, so people skip the check. Three formulas, run after every pass:

=COUNTA(UNIQUE(Region_clean))     distinct values, is it the number you expect?
=SUMPRODUCT(--(LEN(A2:A17)<>LEN(TRIM(A2:A17))))    rows still carrying stray spaces
=COUNT(Units)  against  =COUNTA(Units)             numbers against everything

On the dirty copy, the distinct-region count went from 6 to 4 after cleaning, and the North total came back to 2,495. Those two numbers are the proof. Without them you have a column that looks cleaner, which is not the same claim.

When the values differ in ways no function can reconcile, "Acme Ltd" against "ACME Limited" against "Acme Co", you have crossed out of cleaning and into entity resolution, which is a different job with a different method.

Edge cases that survive a cleaning pass

Six that get through the obvious fixes.

A different invisible character. 160 is the common one, but zero-width spaces and directional marks also travel through copy and paste. If LEN is still too long after substituting 160, use =CODE(MID(A2,n,1)) to walk along and name whatever is actually sitting there.

Apostrophes and quotes that are not the ones you typed. A curly apostrophe and a straight one are different characters, so "O'Brien" from Word never matches "O'Brien" from your keyboard. Substitute one for the other before comparing.

A leading apostrophe you cannot see. Typing '1001 makes Excel store text and hide the apostrophe. LEN does not count it and ISTEXT does report it, which is why the numeric check in section seven exists.

Cleaning that changed the meaning. TRIM collapsing internal runs is right for names and wrong for anything where the spacing carried information, like fixed-width codes. Check a sample against the source before applying it to a whole column.

The formulas that are now pointing at the raw column. Adding a clean column does nothing until everything downstream reads it. Search the workbook for the old column reference; a half-repointed workbook is worse than an unrepointed one, because the two halves disagree.

Text that looks numeric in one place and not another. A column can be half numbers and half text after a bad import, and the halves behave differently in every formula. Compare COUNT with COUNTA rather than reading the column, and never trust right-alignment as evidence, because a format can override it.