Convert text to numbers in Excel
The column looks like numbers, but SUM returns 0 and sorts are wrong: they are text. Depending on what clutters them, the conversion takes a second or a morning.
In Excel, step by step
The warning diamond
Select the range, click the yellow diamond, Convert to Number: for clean numbers stored as text.
Text to Columns
Data, Text to Columns, Finish: reads the whole column again. Under Advanced, set the decimal and thousands separators if the file comes from another country.
Find and Replace
Replace spaces, currency symbols or thousands separators with nothing, before converting.
Pitfalls
- Non-breaking spaces from web exports can only be replaced by typing Alt+0160 in the Find field.
- "1.234,50" in an English Excel: the point is read as a decimal, the result is wrong with no error.
- A trailing minus sign (1,234.56-), common in accounting exports, is recognised by none of these methods.
Faster, on the whole file
Convert text to numbers converts every column at once: spaces and non-breaking spaces, symbols, English and European formats, trailing signs, accounting parentheses. Codes with leading zeros stay text.
Frequently asked questions
- How do I know a number is stored as text?
=ISTEXT(A2)returns TRUE. By eye: it is left-aligned, and often marked with a green triangle.