Fix a lookup broken by invisible spaces
Two values that look identical and refuse to match. This is the most-reported and least-understood spreadsheet bug there is.
The data
| Row number | A | B |
|---|---|---|
| 1 | Imported code | Clean code |
| 2 | A-101 | |
| 3 | A-102 | |
| 4 | A-103 | |
| 5 | A-104 |
Your task
The codes in column A carry stray spaces that break every lookup against them. Put a formula in B2 that returns the cleaned code.
Your formula goes in B2
The checker compares your formula against the accepted answers — it does not run it. The expected result is shown with the solution so you can verify it in your own spreadsheet.
Why this is the right formula
TRIM removes leading and trailing spaces and collapses runs of internal ones to a single space. Before reaching for it, =LEN(A2)-LEN(TRIM(A2)) tells you how many stray characters a cell carries, which is how you prove the problem is whitespace rather than the data. These sample codes contain ordinary spaces, so both accepted formulas return the same cleaned code. CLEAN removes control characters 0–31; it does not remove non-breaking spaces. For CHAR(160) from web pastes, use SUBSTITUTE(A2, CHAR(160), " ") before TRIM.
Try next
Practise it on a full dataset
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Questions
Does this actually run my formula?
The checker normalises case, spaces, argument separators and dollar-sign anchors, then compares your formula with a reviewed list of accepted answers. It does not evaluate the formula. Copy the data into your spreadsheet to verify the expected result independently.
Can a correct formula be rejected?
Yes. The accepted list includes several reviewed approaches, but cannot recognise every equivalent formula. Compare an unlisted solution with the expected result in your spreadsheet and use the page feedback if an accepted answer is missing.
Do I need an account?
No. There is no signup, no progress tracking and nothing stored — everything happens in your browser.