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

1. Data generated from ERP / SAP systems is the most common case (to me)
2. Manually input with an apostrophe (‘) – also very common in workplace 😦
3. Cell format as text before inputting
4. 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.

Again, a picture tells more.

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.