How to convert numbers stored as text in Excel
After moving a table from a PDF or another program, numbers may line up on the left or be missed by SUM. Excel is treating those cells as text, not numbers.
1. How to spot it
- A small green triangle in the cell's top-left corner.
- Numbers are left-aligned (numbers normally align right).
- The status bar sum is blank or 0 when you select the column.
2. Method 1: Convert to Number (easiest)
- Select the cells with the green triangle.
- Click the warning icon (yellow diamond with "!") that appears beside the selection.
- Choose Convert to Number. The triangles disappear and the numbers align right.
If the icon doesn't appear, check File → Options → Formulas and make sure background error checking is on.
3. Method 2: the VALUE function
- In an empty column next to the numbers, type
=VALUE(A2)(A2 = the first text-number cell). - Fill down.
- Use the new column, or copy it and Paste Special → Values over the original.
4. If it still won't convert
- There may be spaces or invisible characters around the number.
- Marks such as currency symbols,
%, parentheses(300)or△300can keep a cell from converting. Remove the marks, or see Why PDF tables break in Excel. - This site's converter turns thousands separators, parenthesized negatives, percent signs and currency marks into real numbers, but some cells can still stay as text. Use the methods above to clean them up.
5. Afterwards
Sum the column and compare with the original. → How to check a converted table
Source: Microsoft Support, "Convert numbers stored as text to numbers" https://support.microsoft.com/en-us/Excel/convert-numbers-stored-as-text-to-numbers-in-excel
Last updated: