TRIM removes exactly one character: the plain ASCII space, code 32. Anything else that looks like a space survives it, and the usual survivor is the non-breaking space, CHAR(160), which arrives with almost every value copied out of a web page.
So the fix is normally =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) rather than TRIM on its own. That covers most columns, though values out of a PDF or a database export can carry line breaks and zero-width characters that need a different function again.
Contents
What you are actually looking at
Two cells that look identical on screen, and a lookup that insists they are different.
The symptom always has the same shape. A VLOOKUP returns #N/A for rows that clearly exist, or a COUNTIF says zero for a name you can see in the list. You wrap the column in TRIM, the formula still fails, and the two values still look the same side by side.
Microsoft documents TRIM as removing the 7-bit ASCII space character, value 32, and nothing else. A character that renders as blank space but carries a different code number is, as far as TRIM is concerned, a letter.
| Character | Code | Does TRIM remove it |
|---|---|---|
| Ordinary space | 32 | Yes, leading and trailing |
| Non-breaking space | 160 | No |
| Line break | 10 | No, use CLEAN |
| Tab | 9 | No, use CLEAN |
| Zero-width space | 8203 | No, and CLEAN misses it too |
The last row is the one that costs people an afternoon. A zero-width space takes up no width at all, so the cell looks normal, the value looks normal, the column width tells you nothing, and every comparison fails anyway. Widening the column and staring at the end of the value is the one diagnostic step that never works here, which is unfortunate because it is the first thing everybody tries.
The sample used here
Every formula below refers to this sheet, so it is worth building before reading on.
Open a blank workbook and put the same customer name in the first five rows of column A. Each row carries a different hidden character, and on screen all five look the same.
A3 : John Smith (trailing space, code 32)
A4 : John Smith (non-breaking space between the words)
A5 : John Smith (line break at the end)
A6 : John Smith (zero-width space at the end)
Typing those by hand is fiddly, so build them with formulas in a spare column and paste the results back as values. That way you know exactly what is inside each cell, which is the whole point of a sample.
="John"&CHAR(160)&"Smith"
="John Smith"&CHAR(10)
="John Smith"&UNICHAR(8203)
Try it now — check
Put =LEN(A2) in B2 and fill it down. A clean John Smith is 10 characters, so every row reading 11 has one extra character in it. The rest of this article is about finding out which one.
Sixty seconds first
Three checks that name the character before you start guessing at fixes.
1Compare LEN before and after TRIM
Put =LEN(A2)&" / "&LEN(TRIM(A2)) next to the column. Two equal numbers that are still larger than the visible text mean TRIM found nothing to remove, so the extra character is not an ordinary space. That rules out half the possibilities in ten seconds.
2Ask Excel for the character code
Read the last character with =UNICODE(RIGHT(A2,1)). You get 32 for a plain space, 160 for a non-breaking space, 10 for a line break and 8203 for a zero-width space. Use UNICODE rather than CODE, because CODE cannot report anything above 255 and will mislead you on the last of those.
3Find out how many rows are affected
Drop =SUMPRODUCT(--ISNUMBER(FIND(CHAR(160),A2:A500))) in an empty cell. A count in the hundreds means the column came from one paste and one SUBSTITUTE clears it. A count of three means someone typed those rows by hand.
Which case is yours
Read the row that matches what those three checks told you, and skip the rest.
| What you saw | What it means | Go to |
|---|---|---|
| UNICODE returns 160 | Non-breaking space from a web or HTML paste | Cause 1 |
| UNICODE returns 10 or 9, or the row is unusually tall | Line break or tab inside the cell | Cause 2 |
| LEN dropped correctly, but the lookup still fails | Text and number are being compared | Cause 3 |
| The original column never changed | The TRIM result was never written back | Cause 4 |
| UNICODE returns 8203, or nothing looks wrong at all | Zero-width or another invisible character | Cause 5 |
More than one row can apply to the same column, and that is normal for data assembled from several sources. Work down the table in order, because the first two fixes remove most of what the later ones would otherwise trip over. If none of the rows fit, the character was probably never there at all, and the two situations that produce that illusion are further down. It is also worth noting which row you landed on, because the same source file tends to land on the same row every month, and that is what makes the habits at the end of this article worth setting up.
Cause by cause
Ordered by how often each one turns out to be the answer.
Cause 1 · A non-breaking space came along with the paste
very commonWeb pages use to stop text wrapping at an awkward point, and every one of those lands in your sheet as character 160.
It sits in the middle of names as often as at the end, and it is invisible to TRIM, to Find and Replace typed by hand, and to anyone reading the cell. Reports exported to HTML from an internal system are the other big source.
Try it now — fix
Swap every 160 for a real space first, then let TRIM clean up what is left. The order matters: SUBSTITUTE alone can leave a genuine double space behind.
Without a helper column, copy one of the offending characters out of the formula bar and paste it into the Find box with Replace left empty.
Cause 2 · There is a line break or a tab inside the cell
commonAddresses pasted from an email, values typed with Alt+Enter and anything out of a PDF carry breaks that TRIM will not touch.
Row height gives it away. With wrap text off a line break shows as nothing, but turn wrapping on and the row grows to reveal a second line. CLEAN strips the first 32 non-printing characters, which covers the break at 10 and the tab at 9.
Careful — CLEAN stops at 31
It removes codes 0 through 31 and nothing beyond, so it will not touch the non-breaking space at 160 or the zero-width space at 8203. Reaching for CLEAN when the real problem is 160 is the most common wasted step here.
Try it now — fix
Stack the three in this order. CLEAN takes out the control characters, SUBSTITUTE handles 160, TRIM tidies what is left.
Cause 3 · TRIM worked, and the values are still not equal
commonTRIM always returns text. Feed it a code Excel treated as the number 1024 and you get four characters back, which will never match the number in your lookup table.
Alignment is the tell. Numbers sit against the right edge of a cell by default and text against the left, so a trimmed column that suddenly went left-aligned was converted without anyone asking.
Try it now — check
Put =ISNUMBER(A2)&" / "&ISNUMBER(D2) where A2 is the value you are looking up and D2 is the matching value in the table. One TRUE and one FALSE means spaces were never the problem.
Try it now — fix
Push the result back to a number, or force the other side to text so both ends match.
=TEXT(TRIM(A2),"00000")
Cause 4 · The clean values were never written back
commonThe helper column is correct, the original column is untouched, and every formula in the workbook still points at the original.
It sounds too simple to need a section until it happens on a file with forty columns, where the helper sits off screen and the pivot table keeps refreshing off the original.
Try it now — fix
Copy the helper column, click the first cell of the original column, and use Paste Special with Values selected. Then delete the helper. Pasting formulas over their own source produces a column of #REF!, so Values is not optional here.
Careful — copy the original column somewhere first
Overwriting is not reversible once the file has been saved and closed, and a cleaning formula that was slightly too aggressive only becomes obvious later.
Cause 5 · Something invisible that has no width at all
occasionalZero-width spaces, soft hyphens and byte order marks come out of database exports, translation tools and copied web content.
None of them draw anything, so LEN is the only reliable witness: a ten-letter name reporting eleven characters, with UNICODE on the last one above 8000, puts you here.
Try it now — fix
Substitute the exact code UNICODE reported. These three cover a zero-width space, a soft hyphen and a byte order mark.
=SUBSTITUTE(A2,UNICHAR(173),"")
=SUBSTITUTE(A2,UNICHAR(65279),"")
If none of those match
Two situations where the character was never there in the first place.
The first is a formatted value. A cell can show 1 024 with a gap in the middle purely because of a number format, while the stored value is the plain number 1024. Nothing is in the cell to remove, and clicking it settles the question, because the formula bar shows what is stored rather than what is displayed.
Try it now — check
Select the column and press Ctrl+1 to open Format Cells. If the Number tab shows a custom format containing a space or a thousands separator, switch it to General and watch whether the gap disappears. If it does, the data was always clean.
The second is a trailing space on the other side of the comparison. People check the column they are cleaning and forget the lookup table, so run the three checks against the table key column too.
Careful — TRIM changes the middle of a value too
It collapses any run of internal spaces to one, which is right for names and wrong wherever spacing is deliberate, such as a fixed-width reference code.
Keeping it from coming back
The same file arrives again next month with the same characters in it.
1Paste as text, not as HTML
When copying from a browser, use Paste Special and choose Text rather than the default. Most of the non-breaking spaces never enter the sheet at all, and the formatting you lose was going to be stripped out anyway.
2Keep one cleaning column in the template
A single column holding the CLEAN, SUBSTITUTE and TRIM stack, sitting permanently beside the paste area, turns a twenty-minute investigation into a fill-down. Name it something obvious so the next person does not delete it as clutter.
3Let the import do it instead
Bringing the file in through Data, Get Data, From Text/CSV puts a query step between the file and the sheet, and Transform offers a Trim and a Clean that run on every refresh. More setup on day one, no work at all from the second month. It also survives the person who built it leaving, which a helper column three screens to the right does not.
Questions that come up
Things this article did not have a place for.
Q.Why does Find and Replace with a space in the Find box not work either?
A.Because the space you type is character 32 and the one in the cell is not. Find and Replace matches the actual character, exactly as TRIM does. Copy the offending character out of the formula bar into the Find box and the same dialog finds every one of them.
Q.Should I just wrap everything in CLEAN and TRIM every time?
A.For names and free text that is a reasonable default. For anything where spacing carries meaning it is not: a multi-line address stored deliberately in one cell loses its breaks to CLEAN and becomes one run of words.
Q.Does TRIM remove spaces in the middle of a value?
A.It reduces them rather than removing them. A run of several spaces between words becomes one, while leading and trailing runs go entirely. To remove every space including internal ones, use SUBSTITUTE with an empty replacement.
Q.The column looks clean now but VLOOKUP still returns #N/A.
A.Check the lookup table rather than the lookup value, then check whether one side is text and the other a number. Those two account for nearly everything left once the invisible characters are gone.
Before you delete the helper column
Six things worth confirming while the original data is still recoverable.
Work down the list in order
□ LEN on a sample row now matches the number of visible characters
□ The three checks were run on the lookup table, not only on the source column
□ Nothing that should be a number is now sitting left-aligned
□ The clean values were pasted back as values, not left in a helper column
□ A copy of the original column exists somewhere in the file
□ Any deliberate internal spacing survived TRIM
Once a column has produced one non-breaking space it will produce more, because the source that made it has not changed. That is why the third habit above repays its setup time so quickly: a query step keeps cleaning the file long after everyone has forgotten why it was needed. The one thing worth writing down somewhere is which character it was, because next month the same column will hand you the same one and the sixty seconds at the top of this article turn into five.
#ExcelTRIM #NonBreakingSpace #ExcelDataCleanup #SUBSTITUTE #ExcelCLEAN
