How to Clean Messy Data in Excel: TRIM, CLEAN and Removing Duplicates

Most "broken" formulas aren't actually broken — they're being fed messy data that only looks clean. TRIM removes extra spaces, CLEAN strips out invisible non-printing characters, and Remove Duplicates gets rid of exact repeat rows. Running your data through these before building formulas on top of it will fix more mystery errors than almost anything else in this series.

If you've been reading along with this blog, you've probably noticed a pattern by now — nearly every "why isn't my formula working" section across these posts eventually points at the same culprit: extra spaces, mismatched text and numbers, or hidden characters sitting in cells that look completely normal. I decided this one deserved its own post because it's honestly the root cause behind more support requests I've fielded from colleagues over the years than any single formula issue. I once spent the better part of a day convinced a VLOOKUP was broken, checking the formula itself over and over, before realising the client ID column had been exported from another system with an invisible line-break character tucked onto the end of every single value. The formula was never the problem.

Why do two cells that look identical not match in a formula?

This is the core issue this whole post is about, so it's worth answering directly. Cells can contain characters that are invisible on screen but very much present to Excel — trailing spaces, leading spaces, or non-printing characters left over from a data export, a copy-paste from a website, or an old system. Two cells can display "NSW" identically and still fail to match each other in a VLOOKUP, SUMIF, or IF formula, because one of them secretly contains "NSW " with a trailing space.

How does TRIM fix this?

TRIM removes leading and trailing spaces from text, and also collapses multiple spaces between words down to a single space.

=TRIM(A2)

If A2 contains " NSW " with spaces on both sides, TRIM returns "NSW" with nothing extra. This is usually best done in a helper column next to your messy data, rather than trying to edit hundreds of cells by hand.

What does CLEAN actually remove that TRIM doesn't?

CLEAN strips out non-printing characters that sometimes come through in data exported from other systems or copied from web pages — things like line breaks or control characters that TRIM won't touch, since TRIM only deals with regular spaces.

=CLEAN(A2)

For genuinely messy imported data, it's common to use both together:

=TRIM(CLEAN(A2))

This handles spacing issues and invisible characters in one pass, which covers the majority of "this should obviously match but doesn't" problems.

How do I actually apply TRIM to my real data, not just a helper column?

Once you've confirmed TRIM and CLEAN fix the problem in a helper column, you'll usually want to replace the original messy column with the cleaned version, rather than keeping formulas sitting alongside your raw data forever.

1. Add your TRIM/CLEAN formula in a helper column next to the original
2. Select the entire helper column and copy it (Ctrl+C)
3. Right-click the original column and choose Paste Special > Values — this pastes the cleaned text as plain values, not formulas
4. Delete the helper column once you've confirmed the original column now holds the cleaned data

Skipping the Paste Special step is a common mistake — if you just delete the helper column without converting it to values first, you'll lose your cleaned data along with it, since the formulas were referencing cells that no longer exist.

How do I split messy data into separate columns, like a full name into first and last name?

This is where Text to Columns earns its place, and it's worth knowing even though it's a tool rather than a formula.

1. Select the column containing the combined data
2. Go to Data > Text to Columns
3. Choose Delimited if your data is separated by something consistent (like a space or comma), or Fixed Width if every entry follows the same character-position pattern
4. Pick the delimiter (space, comma, etc.) and preview how it'll split
5. Choose where the split columns should land, and click Finish

A quicker modern alternative for simple splits is the TEXTBEFORE and TEXTAFTER functions (available in Microsoft 365), which split text without altering your original column at all:

=TEXTBEFORE(A2, " ") returns everything before the first space, and =TEXTAFTER(A2, " ") returns everything after it — handy for splitting a full name into first and last without needing the Text to Columns wizard.

How do I find and remove duplicate rows properly?

Excel has a built-in tool for this, though it's worth understanding what it actually checks before relying on it.

1. Select your data range
2. Go to Data > Remove Duplicates
3. Tick which columns should be checked for duplicates — if you tick every column, only rows that are identical across all of them get removed; if you tick just one column, any repeat in that column alone counts as a duplicate

The catch: Remove Duplicates checks for exact matches. If one entry is "Acme Corp" and another is "Acme Corp " with a trailing space, Excel sees them as different and won't flag either as a duplicate — which is exactly why running TRIM and CLEAN first, before removing duplicates, matters more than people expect.

If you've read my post on "SUMIF, COUNTIF and SUMIFS", you'll remember COUNTIF is also a handy way to spot duplicates without removing anything, which is useful when you want to review what's duplicated before deleting it outright.

FAQ

Why does Remove Duplicates miss obvious repeat entries? Almost always trailing spaces, inconsistent capitalisation in some edge cases, or invisible characters making entries technically different even though they look identical. Run TRIM and CLEAN first, convert to values, then run Remove Duplicates.

Can TRIM fix numbers that are stored as text? No — TRIM only affects spacing within text, it doesn't convert a text-formatted number into a genuine number. For that, multiply the cell by 1 (=A2*1) or use VALUE(A2), or use Text to Columns with no delimiter selected, which often forces a re-evaluation of the data type.

Is it safe to delete my original messy column after cleaning it? Only after you've converted your cleaned helper column to values with Paste Special, and double-checked the cleaned data looks correct. Deleting the original column while formulas still reference it will break those formulas.

Does CLEAN remove all types of hidden characters? It removes most standard non-printing control characters, but some data exports use non-breaking spaces (a character that looks like a normal space but isn't), which CLEAN won't catch. For those, a formula like =SUBSTITUTE(A2, CHAR(160), " ") combined with TRIM usually solves it.

Should I clean my data every time, even if a formula seems to be working fine? Not necessary if everything's matching correctly already. But if you're about to build a workbook that other people will add data to over time — new rows pasted in from various sources — it's worth building the cleaning step into your process early, rather than only troubleshooting it after something eventually breaks.

Is messy data the reason my VLOOKUP or XLOOKUP keeps returning #N/A? Very often, yes. If you've read my post on "VLOOKUP vs XLOOKUP", trailing spaces and text-versus-number mismatches are two of the most common causes listed there, and both are exactly what TRIM and CLEAN are built to fix before the lookup even runs.