Excel: fix numbers stored as text
If Excel will not add up a column, or your numbers sit on the left of the cell instead of the right, they are probably stored as text. Excel sees them as words that merely look like numbers. This often happens with data copied from a website, email or another system.
How to spot it
- The numbers are left-aligned.
- A small green triangle sits in the corner of the cell.
- The total at the bottom of the screen (the status bar) shows a count but no sum.
- Formulas such as SUM ignore the cells, and lookups fail to match.
Fix 1: the quick warning icon
- Select the affected cells.
- Click the yellow warning icon that appears beside them.
- Choose Convert to Number.
Fix 2: Text to Columns
This works well for a whole column at once.
- Select the column.
- Click the Data tab, then Text to Columns.
- Click Finish straight away, without changing any options.
Fix 3: multiply by one
- Type 1 in any empty cell and copy it with Ctrl+C.
- Select the numbers.
- Right-click, choose Paste Special, then pick Multiply and click OK.
- Delete the cell holding the 1.
If the cells are formatted as Text
On the Home tab, change the format drop-down from Text to General. Changing the format does not alter existing entries. After that, click in each cell and press Enter, or use Fix 2.
Hidden spaces
If the fixes above do not work, there may be spaces or odd characters. In a spare column, type =TRIM(A2) and copy it down. This removes extra spaces. Then copy the results and paste them back as values using Paste Special, then Values.
Things to watch
- Telephone numbers, account numbers and postcodes should stay as text, so leave them alone.
- Make a copy of the file before you try anything new.
On a Mac the same options appear on the Data and Home tabs.
When to get more help
If a whole file of imported figures will not behave, send it to us. We can usually clean it up quickly and show you how.