Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”
Prevent GIGO
This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.
Looking for Numbers Stored as Text
As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.”
If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!
Use the ISTEXT() or ISNUMBER() Functions
An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.
Use Conditional Formatting to Highlight Cells with Number Stored as Text
I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.
Use Paste Special – Add
The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!
Learn how to “Master Excel in Minutes – Not Months!”
Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels
How to Find and Change Numbers Stored as Text in Excel [ 7:19 ] Play Now | Play in Popup | Download (1308)
Hi,
I am new to this forum, to avoid number stored as text error just uncheck the rules 1, 3,4 & 8 which under the heading Error checking rules under formulas options in Excel options dialog box. If you do that, you dont need to use VBA macro or manually converting things etc..
Appreciate the tip, very useful!