Why do identical-looking codes fail to match? Check hidden spaces
Join codes that fail to match because of spaces before or after the value. Keep the original text and check any remaining mismatches.
If a code matches only after you retype it, a hidden space may be the reason. Open Key value settings in JOIN SHEET and enable Ignore surrounding spaces to compare text codes without spaces before or after them. You can make the join without rewriting the original values.
Why retyping a code can appear to fix it
Copied or exported codes can contain ordinary spaces or non-breaking spaces that are difficult to see. Retyping a code may leave out that hidden character, which explains why the new value matches. Checking for surrounding spaces can save you from retyping each unmatched code.
This fictional example includes a space before and after P10, a trailing non-breaking space after P20, and an internal space in P 30. The Product sheet contains P10, P20 and P30 without spaces.
Ignore only the surrounding spaces
- Place Orders on the left and Product on the right. Join Order code to Product code and choose All rows from the left sheet.
- Open Key value settings from the join's line menu.
- Enable Ignore surrounding spaces and select Apply. Leave letter-case handling unchanged for this example.
- Check the matched product names and the remaining blanks in Result preview.
Two orders match; one still needs checking
| Order code condition | Product code | Result |
|---|---|---|
| Spaces around P10 | P10 | Item A matches |
| Non-breaking space after P20 | P20 | Item B matches |
| Space inside P 30 | P30 | No match |
All three orders remain, but only two receive product details. The option does not remove the internal space between P and 30. If P 30 and P30 are meant to be identical, apply the relevant cleanup rule in the source.
Does the option clean the whole file?
The setting affects matching between these two sheets' text join keys. It leaves the original spaces visible in both source and result values. It does not clean the whole file or change separate Compare values lines.
If a code still does not match, inspect it for an internal space, a different symbol or a damaged character. After enabling the option, run Check join again: codes that differed only by surrounding spaces may now produce several matches.
For a letter-case issue instead, see matching uppercase and lowercase codes.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
VLOOKUP Error Stumper: Strange Formatting in Lookup Column — Reddit r/excel
The codes here are fictional examples of the hidden-space problem raised in the question.
