If a statement amount looks perfect but gives #VALUE! or refuses to convert, a non-breaking space is the likeliest cause. TRIM removes only the ordinary space, character 32, so it leaves character 160 behind. Use =SUBSTITUTE(C2,CHAR(160),"") to strip it, then wrap the result in VALUE.
Why does TRIM not remove this space?
Web pages and some PDF exports use a non-breaking space, so a line does not wrap in the middle of a figure. It looks exactly like a normal space, but its character code is 160, not 32. TRIM, CLEAN and Find and Replace with a typed space all miss it. CLEAN only removes the non-printing characters with codes 0 to 31, so it is no help either.
How do you prove it is the cause?
Take one stubborn cell, say C2 showing 1,250.00. Run three tests in empty cells:
- =LEN(C2) should be 8 for "1,250.00". If it returns 9, one invisible character is in there.
- =CODE(RIGHT(C2,1)) tells you the code of the last character. 160 means non-breaking space. A digit would return a code between 48 and 57.
- =CODE(LEFT(C2,1)) does the same for the first character, in case the space leads.
If the space is in the middle, test one position at a time with =CODE(MID(C2,4,1)), changing 4 to each position. Or use =UNICODE(MID(C2,4,1)), which also reports codes above 255.
| Check | Clean cell | Cell with hidden space |
|---|---|---|
| What you see | 1,250.00 | 1,250.00 |
| =LEN(C2) | 8 | 9 |
| =CODE(RIGHT(C2,1)) | 48 | 160 |
| =VALUE(SUBSTITUTE(C2,",","")) | 1250 | #VALUE! |
| =VALUE(SUBSTITUTE(SUBSTITUTE(C2,CHAR(160),""),",","")) | 1250 | 1250 |
The fix, in one formula
Chain the clean-up so every known culprit is handled:
- =VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(C2,CHAR(160),""),",","")))
- SUBSTITUTE with CHAR(160) removes the non-breaking space.
- The second SUBSTITUTE removes commas.
- TRIM catches ordinary leading and trailing spaces.
- VALUE turns the cleaned text into a number.
If CHAR(160) does not behave on your machine, which can happen on some Mac versions, use UNICHAR(160) instead. Both return the same character on the systems where each works.
What if it is not a non-breaking space?
Other invisible characters exist. A zero-width space has code 8203 and you can remove it with =SUBSTITUTE(C2,UNICHAR(8203),""). Tabs have code 9 and line breaks code 10, and CLEAN handles those. Work through the codes you find with CODE or UNICODE, remove each in turn, and the cell will eventually convert.
Worked example
Say your statement has 120 rows and a SUM of 0 even after conversion. Test =SUMPRODUCT(--(LEN(C2:C121)<>LEN(TRIM(C2:C121)))) to find padded cells. If this returns 0 but conversion still fails, test =SUMPRODUCT(--ISNUMBER(SEARCH(CHAR(160),C2:C121))), which counts cells that contain the non-breaking space. Say the answer is 120: every row has it, so one substitute in a helper column cures all of them.
Can Find and Replace remove it without a formula?
On Windows, yes. Press Ctrl+H, click in Find what, hold Alt and type 0160 on the numeric keypad, then release. The box looks empty, but it now holds the non-breaking space. Leave Replace with empty and press Replace All. Do it on a copy, because the replacement is not tied to any one row.
If the data returns every month, keep the SUBSTITUTE formula in a column of an Excel Table instead. Paste the next raw amounts into the original column and the cleaning formula fills itself down the new rows, so the same invisible character never trips you twice.
Next-time checklist
- Copy a stubborn cell and measure it with LEN.
- Look up the last and first character with CODE.
- Build the cleaning formula in a helper column.
- Confirm COUNT equals COUNTA, then paste values.
If your statements are always messy, SpendixAI takes PDF, CSV or Excel uploads and shows the rows for review before they are saved; some layouts will still need a careful look.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


