Number Stored as Text…

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.

Excel Tips - Number Stored as Text4

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.

Excel Tips - Number Stored as Text 3

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:

Excel Tips - Number stored as text 5.png

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.

Excel Tips - Number stored as text 6.png

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.Excel Tips - Number Stored as Text 2

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:

Excel Tips - Number Stored as Text 4

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

Excel Tips - Number Stored as Text 3

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)Excel Tips - Number stored as text 7.png

Here we go!

Excel Tips - Number stored as text 8.png

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

Excel Tips - Number stored as text 9.png

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

Excel Tips - Number stored as text 10.png

We may hide the helper column afterward, of course.

  • Using array formula

Excel Tips - Number stored as text 11.png

How it works?

The range A2:A6 contains both text and number.  Excel evaluates the formula as follow: Excel Tips - number stoed as text 12.png

Then it applies the double negation to each of the element, and thus converting number stored as text to number: Excel Tips - number stored as text 13.png

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.


This entry was posted in Excel Basic, Formula and tagged , , . Bookmark the permalink.

5 Responses to Number Stored as Text…

  1. Arnstein says:

    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.


    • MF says:

      Hi Arnstein,
      Glad you like it. For your example, I think you don’t need array input. Just =- -A2 would do the trick.


  2. Steve says:

    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 ?


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s