Use =TRIM(A2) to remove ordinary spaces at the beginning and end of text and reduce repeated spaces between words to one. If the result still doesn't match another cell, check the actual character before adding another cleanup formula. A nonbreaking space can look just like an ordinary one.
There's also a catch if you use VBA: its identically named Trim function leaves internal spaces alone. That difference tripped me up once upon a time. Here's the comparison, followed by a way to find the characters that worksheet TRIM leaves behind.
Download the hidden-character practice workbook to compare the source text, character codes, and cleaned results. The workbook is macro-free; the short VBA comparison below runs separately in the Immediate Window.
Compare worksheet TRIM with VBA Trim
Suppose A2 contains North Bay with two ordinary spaces before North, three between the words, and one after Bay. The brackets below mark the ends; they aren't part of the cell.
[ North Bay ]Enter this in D2:
=TRIM(A2)The result is North Bay. Worksheet TRIM keeps a single ordinary space between the words. It doesn't join them into NorthBay.
In the VBA editor's Immediate Window, run these lines separately:
? "[" & Trim(" North Bay ") & "]"
? "[" & Application.WorksheetFunction.Trim(" North Bay ") & "]"The output is:
[North Bay]
[North Bay]The first line removes only the spaces at the two ends. The second calls Excel's worksheet function, so it also reduces the three internal spaces to one. Microsoft's VBA Trim documentation describes the leading-and-trailing behavior.
Use the worksheet version from VBA when you want that internal-space cleanup. Keep VBA Trim when the internal spacing must stay as it is. Neither choice makes every invisible character disappear.
Why TRIM sometimes appears not to work
Microsoft documents that Excel TRIM targets ASCII space 32. It doesn't remove the nonbreaking space, Unicode 160, commonly found in copied web text.
These four cases need different decisions:
| What you see | Character to check | Appropriate fix |
|---|---|---|
| Extra spaces at the ends or between words | Ordinary space, 32 | TRIM removes the ends and reduces repeated internal spaces. |
| A gap that survives TRIM | Nonbreaking space, 160 | Replace it with an ordinary space, then use TRIM. |
| A key looks right but has an extra invisible character | Zero-width space, 8203 | Remove that character if it doesn't belong in the key. |
| Imported text contains a tab | Tab, 9 | Replace it with a space if it separates words; CLEAN deletes it. |
Those are examples to diagnose, not a list of characters you should strip from every file. Preserve the originals in column A and put the repair in a helper column.
Find the character with LEN, MID, and UNICODE
For a small example, use these source values in A2:A6. The character descriptions explain what's really in the cell.
| Cell | Source content | Character count |
|---|---|---|
| A2 | Two spaces + North + three spaces + Bay + one space | 14 |
| A3 | North + one nonbreaking space + Bay | 9 |
| A4 | North + one zero-width space + Bay | 9 |
| A5 | North + one tab + Bay | 9 |
| A6 | North + one ordinary space + Bay | 9 |
If you're building the example yourself, enter ="North"&UNICHAR(160)&"Bay" in A3, ="North"&UNICHAR(8203)&"Bay" in A4, and ="North"&UNICHAR(9)&"Bay" in A5. These formulas create the specified characters without relying on copying something invisible.
In B2, enter the LEN check and copy it through B6:
=LEN(A2)A2 returns 14. The other four rows each return 9. That tells you why length alone isn't enough: a normal space, a nonbreaking space, and a zero-width space can each occupy one character in these strings.
In A3, the questionable character follows the five letters in North. Enter this in C3 to select the sixth character with MID and return its code point with UNICODE:
=UNICODE(MID(A3,6,1))C3 returns 160. Copy the formula down through C6: C4 returns 8203, C5 returns 9, and C6 returns 32. UNICODE reads the first character of its argument, so =UNICODE(A3) would inspect the N, not the hidden separator. Adjust the position when your own data has a different layout.
Apply the repair that matches the character
For the nonbreaking space in A3, enter this in D3:
=TRIM(SUBSTITUTE(A3,UNICHAR(160)," "))UNICHAR supplies the character numbered 160. SUBSTITUTE replaces it with the ordinary space inside " ", and TRIM handles any extra ordinary spaces. The result is North Bay.
For the unwanted zero-width space in A4, enter this in D4:
=SUBSTITUTE(A4,UNICHAR(8203),"")The empty replacement "" removes that character, giving NorthBay. This is appropriate only if the intended key has no separator there.
For the tab in A5, CLEAN gives a different result from a replacement. Enter this optional comparison in D9, keeping D5 available for the tab-to-space repair:
=CLEAN(A5)D9 returns NorthBay, because CLEAN deletes the tab. If the tab separates two words, enter this replacement formula in D5:
=SUBSTITUTE(A5,UNICHAR(9)," ")Now the result is North Bay. As Microsoft's CLEAN documentation explains, CLEAN targets the first 32 nonprinting ASCII characters, 0–31. It isn't a general Unicode cleanup function.
Check the cleaned values before replacing the originals
Compare the helper column with the source, including rows where spaces are meaningful. Removing every space can turn two different identifiers into the same one; deleting a tab can join words that should stay separate.
If this is part of a failed lookup, check the source keys as well as the value you're searching for. The VLOOKUP text troubleshooting guide shows how to clean both sides while preserving numeric and text identifiers. If a cleaned amount is still stored as text, convert it to a number using the source decimal convention; keep identifiers with meaningful leading zeros as text. For other imported-data problems, use the Data Cleanup guides to choose the next check.