Numbers Stored as Text, and How to Repair Them

Where they come from

Nobody types a number as text on purpose. It arrives that way: a CSV opened in the wrong locale, a copy-paste out of a PDF or a web page, an export from an accounting system that formats before it exports, a Swiss apostrophe separator (1'250) in a file read as English, a comma decimal in a file read as German, a trailing space, a non-breaking space between the digits and CHF, or a minus sign that is really an en dash from a word processor.

What it costs

Everything downstream is understated rather than wrong-looking. SUM skips the text rows, so the total is too small but perfectly formatted. AVERAGE divides by the count of numeric cells only, so the mean is computed over a subset you did not choose. COUNTIF(range,">1000") ignores the text rows entirely. None of these produce an error, and that is the danger: a report can be short by five figures and pass every visual check.

Repairing them, in order of preference

• Text to Columns (Data ribbon): select the column, Next, Next, set the correct locale under Advanced, Finish. It reconverts the whole column in place and handles the thousands separator. Fastest fix for a one-off file.
• Paste Special · Multiply by 1: type 1 in an empty cell, copy it, select the column, Paste Special → Multiply. Anything convertible becomes numeric. It will not touch entries containing letters such as CHF.
• Formulas in a helper column when you need the fix to be repeatable: VALUE, with SUBSTITUTE stripping separators and TRIM removing stray spaces.
• Power Query (Data → Get & Transform) when the same file arrives every week. You set the column type and the source locale once, and every future refresh is converted the same way with no manual step. This is the only option on the list that survives being handed to somebody else.

=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"'",""),"CHF",""))
=IFERROR(VALUE(SUBSTITUTE(TRIM(B2),"'","")),"CHECK")

The second version is the one to use on real data: it converts what it can and flags what it cannot, so unconvertible cells become visible instead of becoming errors that spread. Note that VALUE and SUBSTITUTE are ordinary Excel functions but are outside this course's lab engine — run these two in Excel itself.

The non-breaking space

If TRIM and VALUE both fail on a cell that visibly contains nothing but digits, the culprit is almost always character 160, the non-breaking space that web pages and PDFs use. TRIM does not remove it, because it is not a normal space. =SUBSTITUTE(B2,CHAR(160),"") does, and it is worth having in the repair chain by default.

After the repair, verify

Never trust a conversion you have not checked. Put =COUNTA(range)-COUNT(range) next to the repaired column: it must be zero. Then compare SUM before and after — the increase is exactly the money that was invisible, and it is worth writing that figure into the sheet, because it is the number that justifies the cleanup to whoever asks why the report changed.