Convert Text to Numbers in Excel

How to Convert Text to Numbers in Excel

1. Add the workbook - Drop an .xlsx, .xls, .xlsm, or .xlsb file. It is read locally in your browser.
2. Choose how values are read - Currency symbols, percentages, accounting parentheses and the decimal separator are all configurable.
3. Decide about leading zeros - Kept as text by default, because those are usually ZIP or product codes.
4. Download the converted workbook - Numeric cells become real numbers, so SUM and AVERAGE finally work.

Why SUM Returns Zero

A cell can look like a number and still be text. Excel marks these with a small green triangle and the warning "Number Stored as Text", and it is what causes the classic symptom: =SUM(B2:B500) returns 0 even though the column is visibly full of figures. SUM ignores text, so it adds nothing.

It usually arrives with the data - CSV imports, database exports, copied web tables, and anything that came out of an accounting system with a currency symbol or a thousands separator baked into the value.

The Leading Zero Problem

The reason this is not simply automatic is that converting text to numbers is destructive for some columns. A ZIP code of 01234 becomes 1234. A product code of 007 becomes 7. There is no way to recover the original from the result, because the zero was never stored.

So values with a leading zero are kept as text by default and counted separately in the summary. If you know the column is genuinely numeric, switch the option off - but check the count first.

Which Separator Is the Decimal Point

1,234 is one thousand two hundred and thirty-four in the US and one-point-two-three-four across much of Europe. Reading it the wrong way is a factor-of-a-thousand error that looks entirely plausible in a total. The detector reads the shape of each value - a comma grouping exactly three digits is a thousands separator, while 1,23 is a decimal - and you can override it for the whole file if your export is consistent.