It is something basic and commonly seen. Nonetheless, it is really important to understand the different behaviors between a number, and a number stored as text, especially if you want to move forward to write effective formula. This post is intended to explain
- What it is?
- How to know if a number is a text or a number?
- Why do we have number stored as text?
- Why does it matter?
- Ways to fix it
First of all, what is number stored as text?
Well, i think the description “Number stored as text” is clear enough. Isn’t it?
Then how do we know if it is a number stored as text?
The obvious signal is the little green triangle on the upper left corner of a cell.
When you select the cell with that little green triangle, you should see a smart tag next to the active cell. Click on it then you will see what it is about. It says “Number Stored as Text” on top.
Isn’t it obvious?
But in reality, it is not always obvious. There may be cases that there is no indication on the cell for a number stored as text. Like below:
Luckily, Excel by default aligns text to the left; number to the right. It means when you see something like the above (a number aligned to the left), there is a high chance that it is actually a number stored as text. To further verify, use a simple function =ISTEXT.
The function returns a result of TRUE where the reference cell is a text. In any doubt, use this logical function to test whether a cell hold a number or a text is the simplest method.
Tip: Try also ISNUMBER
Why are we having number stored as text?
Common cases (to me) where I encountered Number Stored as Text
- Data generated from ERP / SAP systems is the most common case (to me)
- Manually input with an apostrophe (‘) – also very common in workplace 😦
- Cell format as text before inputting
- Cell content returned by function that is designed to return text, e.g. FIXED, LEFT, MID, RIGHT, TRIM, just to name a few.
Why does it matter?
Potential errors for calculation from functions
Think about you have a column of numbers mixed with numbers stored as text and you try to use the simple function SUM to get the total at the end of the column. Unfortunately you won’t get the correct answer as all those numbers stored as text will be ignored by the function by default. It would s quite frustrating indeed as you do not expect Excel to return an “incorrect” answer for such simple calculation. Check HERE for more details.
Different behaviors in calculation
It is all about how Excel treats number stored as text in calculation.
In short,
- Most built-in functions ignore text, and thus ignore number stored as text
- A mathematical operator would however treat number stored as text as number, and thus turn the result into number. See below examples:
IMPORTANT: When you try to convert number stored as text to number by this method, pay extra attention in typing… A typo may lead to alteration of the original values of data. Think about what if you type =A1*2 mistakenly… 😮
Ways to fix it – convert number stored as text to number
When original data can be modified directly
- Using smart tag
Remember this screenshot? See the second line? We may convert number stored as text to number right away. It works for a range of cells. Just to make sure you start your selection with a cell carrying the smart tag.
- Using copy and paste special (with an operation)
Select any blank cell -> Copy -> Select the range you want to edit -> Paste Special (with the following options checked)
Here we go!
Why it woks?
The actions essentially add 0 to the range, thus converting number stored as text back to number (in original value).
- Using text to column
Select the range –> Data Tab –> Text to Columns (Select “Delimited” in step 1) -> uncheck all options in step 2 ->Finish
As simple as that! 🙂
The above ways would actually convert number stored as text to number, i.e. Changing the original data. If you want to preserve the data as it is, you may consider the following:
When original data cannot be modified
-
Using helper column
We may hide the helper column afterward, of course.
- Using array formula
How it works?
The range A2:A6 contains both “text“ and number. Excel evaluates the formula as follow:
Then it applies the double negation to each of the element, and thus converting number stored as text to number:
The rest is simple, SUM all elements in the array and return the result.
Note: Hold Ctrl+Shift+Enter for inputting array formula. The {} will appear in the formula bar automatically for array input in this way. DO NOT type the {} by yourself.
Do you encounter Number Stored as Text? How do you tackle them? Please share in comment.
VALUE() and DATEVALUE() are less grotesque than adding 0.
The text format of cells is one of Microsoft’s most inept stupidities. All other formats do not change the functional nature of cell contents. Many competent users go through life thinking that format strictly affects display rather than functionality, which is sane and logical – and might even be taught in a course or reference text – but are ignominiously torched by Excel’s idiotic implementation of Text format. Text format causes massive waste of time and productive existence, consistent with Microsoft’s mission.
LikeLike
Using array formula
Greate tip!!
I tried:
A2 200 as textvalue
A3 300 as textvalue
A4 400 as textvalue
In B2 I used the formula =–a2 and Ctrl+Shift+Enter (double unary before cell adress)
and then I copied down to B4.
Then you can copy b2:b4 and past special as value.
LikeLike
Hi Arnstein,
Glad you like it. For your example, I think you don’t need array input. Just =- -A2 would do the trick.
Cheers,
LikeLike
Do you know which of these methods is the quickest or most efficient. The convert to number in the smart tag alway seem very slow for large numbers of row ?
LikeLike
Hi Steve,
Good question!
try the text to columns . I didn’t test it but i think it should be fast enough.
Please share your findings. 😁
LikeLike
btw, if you have multiple columns, try the copy and paste value method.
LikeLike