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
- Select any empty cell anywhere
- Enter the value 1 into that cell
- Copy that cell (Ctrl+C)
- Select all the cells which needs to be converted
- Click Paste Special (one of the option in the right
click menu)
- 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.
