A product name looks identical in two files but Excel won't match them in a lookup; could a hidden space be the cause?
Often, yes. A non-breaking space (NBSP) that comes along with text copied from a web page or Word looks exactly like a normal space but is a different character, so Excel sees 'Arabic coffee' in the two files as two different texts. The whitespace cleaner's rule 'Replace non-breaking and other Unicode spaces with a normal space' is on by default, turns it into an ordinary space and shows how many it replaced.
How to confirm it is a hidden space
Paste the name from the first file into the Whitespace cleaner and look at the counter next to the special-spaces rule. If it shows more than zero, you have found the cause. To see it with your own eyes, turn on 'Show invisible characters in the preview': an ordinary space shows as a middle dot, and a non-breaking space shows as a different mark, ⍽.
The name 'Arabic coffee' copied from a supplier's page, with a non-breaking space between the words.
With the default rules: Total changes 1, all from the special-spaces rule, and the result is 'Arabic coffee' with a normal space.
A three-line column with two such spaces: 'Arabic coffee 250 g', 'Green tea', 'Sukkari dates'. The cleaner replaces 2, and the middle line is untouched.
Why Word and websites add it
A non-breaking space exists to stop a line from breaking at that point, so a number stays with its unit or a name with its title. Word processors insert it automatically in some places, and web pages use it a lot for layout. When you copy the text it comes along, and nothing on screen tells it apart.
Fixing a whole column
- Copy the column that fails to match from Excel and paste it into the whitespace cleaner.
- Keep the default rules. The special-spaces rule replaces the non-breaking space, and the two trim rules remove any whitespace at the edges of each cell.
- Check the change count, then copy the result and paste it over the old column.
- Run your lookup or match again in Excel.
Not only the non-breaking space
The same rule replaces other special Unicode spaces, such as the thin space that sometimes sits between a price and its currency. We tried '150 SAR' with a thin space and the counter showed 1 as well. Zero-width characters are not covered by this rule; they have their own rule, off by default, because they are not spaces but characters with no width at all.
If you want proof that cleaning changed nothing but spaces, compare the text before and after with Text comparison at the Characters level.
When not to replace it
If you are copying text to publish on a page and rely on the non-breaking space to keep '150 SAR' on one line, replacing it lets the line break there. In that case untick the rule before copying. For matching, searching and spreadsheets, replacing is what you want.
Common questions
Does the tool change my Excel file itself?
No, it works on the text you paste, and you paste the result back into the file.
Does cleaning remove normal single spaces between words?
No, a single space stays. Collapsing only affects repeated spaces.
Is my text sent to a server?
No, cleaning runs inside your browser.