Numbers
How to Convert Text to Numbers in Excel (Numbers Stored as Text)
Select the cells and click the yellow warning icon, then "Convert to Number". If there is no icon, type 1 in an empty cell, copy it, select your numbers, and use Paste Special, then Multiply. If the numbers still will not add up, they contain hidden spaces: clean them with =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))).
When a SUM shows 0, a number will not sort properly, or a VLOOKUP says it cannot find a value that is plainly there, the usual cause is numbers stored as text. They look right but Excel treats them like words. This guide shows how to recognise the problem and four ways to fix it.
How to tell a number is really text
- It sits against the left edge of the cell, where text goes (numbers go right by default).
- A small green triangle appears in the corner, with a yellow warning icon when you select the cell.
- The status bar shows only "Count" and not "Sum" for a selected range.
=ISNUMBER(A2)returns FALSE.
Fix 1: Convert to Number (quickest)
Select the cells. Click the yellow warning icon that appears, then choose Convert to Number. Done.
Fix 2: Paste Special, Multiply (works without the icon)
- Type
1in any empty cell and copy it (Ctrl+C). - Select your text numbers.
- Choose Home, Paste, Paste Special, Multiply, OK.
Multiplying by 1 forces Excel to read each value as a number. Delete the helper cell afterwards.
Fix 3: Text to Columns
Select the column, choose Data, Text to Columns, and click Finish without changing anything. Excel re-reads each cell and converts what it can.
Fix 4: the VALUE formula
=VALUE(A2)Fill it down, copy the results and Paste Special, Values, over the originals. VALUE returns #VALUE! for any cell it cannot read, which makes the stubborn ones easy to find.
When none of these work: hidden characters
Data copied from websites, PDFs and other systems often carries invisible characters. The usual culprit is the non-breaking space (character 160), which TRIM does not remove. This formula handles it, along with ordinary spaces and non-printing characters:
=VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))Other things that stop a number being a number:
| In the cell | Problem | Fix |
|---|---|---|
1,234.50 in a region that uses 1.234,50 | The thousands and decimal marks are swapped | Use Find and Replace, or Text to Columns with the right separators |
$1,200 typed as text | A currency symbol inside text | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) |
'123 | A leading apostrophe forces text | Fix 1 or Fix 2 removes it |
1O0 | The letter O instead of the digit 0 | Find and Replace, then check by eye: this is a typo, not a format |
12345678901234567890 | More than 15 digits | Excel keeps only 15 significant digits; keep such IDs as text on purpose |
Tip: IDs, phone numbers, postcodes and account numbers are supposed to be text. Do not convert them: leading zeros would be lost. See fixing phone numbers in Excel.
Stop it coming back
Format the column as Text before you paste IDs into it, and as Number or Currency before you paste amounts. When you import a CSV, use Data, From Text/CSV and set each column type in the preview instead of letting Excel guess.
When the file is large or mixed
Columns with a mixture of real numbers, text numbers and typos are slow to fix by hand. DecodingPro reads each column, fixes the safe cases and flags values like 1O0 for you to confirm.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Why does my SUM show 0?
SUM ignores text. If every number in the range is stored as text, the total is 0. Convert them with Fix 1 or Fix 2.
Will converting remove my decimal places?
No. The decimals stay. Only the display format controls how many are shown.
Can I convert an entire sheet at once?
You can select the whole used range and use Convert to Number or Paste Special, Multiply. Do not do it on columns that hold IDs or phone numbers.