Spaces in CSV cells breaking VLOOKUP and imports

Two cells both say ACME-1001. VLOOKUP returns #N/A, a duplicate check passes when it shouldn't, and the import rejects a value as "not found." One of the cells is actually ACME-1001 with a space you can't see.

A2: "ACME-1001" (9 characters) B2: "ACME-1001 " (10 characters) → not equal

Why it happens

CSVs come from exports, forms and copy and paste, where spaces slip in around values: padded fixed-width columns, spaces after commas (Ana, Lisbon), or text copied from a web page. Two kinds cause trouble:

• Regular spaces at the start or end of a value.
• Non-breaking spaces (character code 160), common in text copied from web pages. They look identical but Excel's TRIM does not remove them.

Detect it

In a helper column: =LEN(A2)<>LEN(TRIM(A2)) returns TRUE for cells with extra regular spaces. To catch non-breaking spaces: =ISNUMBER(FIND(CHAR(160),A2)).

Remove it in Excel

1. In a new column, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). That converts non-breaking spaces to regular ones, then trims.
2. Fill down.
3. Copy the new column, then Paste Special ▸ Values over the original.
4. Delete the helper column.

Note that TRIM also collapses runs of spaces inside a value into one, so New York becomes New York. Usually harmless, sometimes not.

Remove it in Google Sheets

Select the range and use Data ▸ Data cleanup ▸ Trim whitespace. Or use the same TRIM formula in a helper column.

Don't forget the headers

A header like Email with a trailing space becomes a column name that code can't find and a database import maps to nothing. Check the first row too.

Where this bites in practice

Lookups are the obvious case, but not the only one. A pivot table lists Lisbon and Lisbon as two separate items. Remove Duplicates keeps both. A COUNTIF returns 0 for a value you can see in the list. A database with a unique constraint accepts [email protected] twice, once with a trailing space, and then sends two emails. A login or API lookup fails because the key in your file doesn't match the key in the system. Cleaning whitespace before any of these steps is cheap; hunting the cause afterwards is not.

Other invisible characters

If TRIM and SUBSTITUTE(…,CHAR(160)," ") don't fix a mismatch, the cell may contain a zero-width space, a line break or a tab. CLEAN removes line breaks and other non-printing characters below code 32. Compare lengths with LEN and inspect individual characters with CODE(MID(A2,n,1)) or UNICODE to see what the extra character is. Zero-width spaces (code 8203) can be removed with SUBSTITUTE(A2,UNICHAR(8203),"").

A habit that saves time

Make trimming the first step whenever you receive a CSV from someone else, before sorting, joining or de-duplicating. Wrap lookup keys in TRIM inside the formula too, as in =VLOOKUP(TRIM(A2),Table,2,FALSE), so one stray space on either side doesn't return an error.

← Back to the CSV Fixer tool