Convert Text Formatted Numbers to Actual Numbers

When I download reports from other programs such as SAP, often numbers are imported as text. Such numbers will be aligned left (common for actual text) and often indicated by error indicator (small green triangle on top left of those cells). These cells cannot be referenced in calculation formulas as Excel thinks it is actual text. Sometimes formatting the cells as Numbers works fine. Few times, it doesn't work. If I do F2 (edit the cell) and press Enter, then the cell value will be recognized as number. But doing this over thousands of cells manually will be irksome. If it sounds familiar to you, read on to see what we can do about it.

Excel 2002 and later versions are programmed to identify such cases and automatically convert these values to numbers. But if this hasn't happened by any reasons, we have few solutions below.

Solution 1: Try Formatting

Changing the format of the cells might work sometimes. So let’s try it. Click Home tab > Number group, select Number in the drop-down (in Excel 2007 or later) or Format menu > Cells > Number tab > under category, select Number (in Excel 2003 or earlier).

Does this solve the problem? If not, read on.

Solution 2: Use Error Checking

If those cells containing numbers as text are showing an error indicator, you may try this solution. Select all those cells. You will be able to see an error button (a square type of button with exclamation mark in it) next to the selection. Click on that and in the menu that appears, select Convert to Number.This action converts the numbers that are stored as text back to numbers.

Solution 3: Multiply by 1

1. Select any empty cell anywhere
2. Enter the value 1 into that cell
3. Copy that cell (Ctrl+C)
4. Select all the cells which needs to be converted
5. Click Paste Special (one of the option in the right click menu)
6. In the Paste Special dialogue box, select the Multiply option, then click OK.

This method forces a multiplication on the existing cells, while doing so Excel converts numbers in disguise to actual numbers.

Solution 4: Use TRIM function

Sometimes space before and after the actual number might cause Excel thinking that value is text. To clean up those spaces, use Excel’s built-in TRIM function.

Solution 5: Use SUBSTITUTE function

Some programs inserts spaces in between two digits in the same number (in one of the report I use, rupees and paise are separated by a space instead of dot). In this case, you can use SUBSTITUTE function to replace that space with a dot.

Solution 6: Use VALUE function

Value function converts any number, date or time formats entered as text to a number. Value function can also clean up the apostrophe normally entered in the start of the cell to show number as text.

Look at those numbers in disguise; they should be back as numbers by now.