Remove non-breaking spaces that TRIM leaves behind
Reproduce a code containing Unicode non-breaking spaces, then replace those characters before trimming ordinary spaces.
The data
| Row number | A | B |
|---|---|---|
| 1 | Imported code | Clean code |
| 2 | =" "&UNICHAR(160)&"A-101"&UNICHAR(160)&" " | |
| 3 | =UNICHAR(160)&"A-102"&UNICHAR(160) | |
| 4 | ="A-103"&UNICHAR(160)&" " |
Paste into A1 on a blank worksheet to create the invisible characters from explicit UNICHAR formulas. Input formulas use English function names and comma separators. B2 is the empty answer cell. Expected values were calculated independently; no native Excel execution is claimed.
Your task
A2 contains ordinary spaces and Unicode non-breaking spaces around A-101. Put a formula in B2 that returns just the code, using SUBSTITUTE and TRIM. UNICHAR(160) identifies the non-breaking space.
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 ordinary ASCII spaces, not the Unicode non-breaking space at code point 160. SUBSTITUTE replaces that known character with an ordinary space; TRIM then removes the leading and trailing spaces. Leaving out the optional instance argument replaces every occurrence. UNICHAR uses the Unicode code point explicitly. CLEAN is optional here and does not replace the non-breaking-space step. This exercise targets U+00A0 only; other invisible Unicode characters require identification and their own rule. Compare the cleaned value with A-101 before using it as a lookup key.
Documentation
Documentation reviewed . This is a source review, not an Excel execution test.
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.