Numbers stored as text in Excel: why SUM ignores them

A number stored as text looks like a number, but Excel holds it as text. SUM skips it, so your total is too small, and Excel shows no error. A lookup for the real number does not find it either.

Here is where it usually starts. A web shop exports last week's orders to Excel. The system writes every amount as text. Someone fixed two amounts by typing them again, and those two are now real numbers. The rest are still text.

In column B, 120 and 75.25 sit on the right of their cells. The other three sit on the left. That is the first clue.

SUM gives the wrong total, and says nothing

SUM ignores text inside a range, even text that looks like a number. The plus sign is different. It turns text like "48.50" into a number before it adds. So the two formulas disagree, and neither one shows an error.

AVERAGE has the same problem. =AVERAGE(B3:B7) gives 97.625. It only sees the 2 real numbers.

How to spot it

• Alignment. Text sits on the left, numbers on the right. This only works if nobody changed the alignment.
• A green triangle in the top-left corner of the cell. Excel adds it to a number stored as text.
• The status bar. Select B3:B7. The bar at the bottom shows Count 5 but Sum 195.25. The sum only includes real numbers.
• A formula. =ISNUMBER(B5) gives FALSE, and =ISTEXT(B5) gives TRUE.

For a whole column, =COUNTA(B3:B7)-COUNT(B3:B7) gives 3. That is 3 cells that are not numbers. The COUNT and COUNTA article explains this check.

Why it happens after an import

• The other system writes the values into the file as text.
• The column was formatted as Text before the data was pasted in.
• A value starts with an apostrophe, such as '48.50. The apostrophe tells Excel to keep it as text. You only see it in the formula bar.
• The number was copied from a web page or a PDF, with extra characters you cannot see.

What does not work: changing the cell format to Number. The format changes how a value looks. The cell still holds text until you type the value again.

Four ways to fix it

1. Convert to Number

Select B3:B7. Start on a cell with a green triangle, such as B3. A small warning button appears next to the selection. Click it and choose Convert to Number.

This is the quickest fix for a few cells. The button only appears when Excel can read the text as a number. So it does not help when the text holds extra characters.

2. Text to Columns

Select column B. Click Data, then Text to Columns, then Finish. Excel reads every cell again, and the text numbers become numbers.

It works on one column at a time. It is the fastest way to fix a long column.

3. Paste Special, multiply by 1

Type 1 in an empty cell and copy it. Select B3:B7. Press Ctrl+Alt+V to open Paste Special. Choose Values and Multiply, then click OK. Delete the 1 afterwards.

Each cell is multiplied by 1, which makes it a number. This works on many columns at once.

4. A formula: VALUE or times 1

To get the right total without changing the data, =SUMPRODUCT(B3:B7*1) gives 299.65. It works in every version of Excel.

Which one I use: for a file you fix once, use Convert to Number or Text to Columns. For an export that arrives every week, keep a VALUE column in the sheet. Then the fix happens each time you paste in new data.

Lookups miss it too

The order numbers in column A came from the same export, so they are text too. Say D3 holds 5103, typed as a number.

The better fix is to convert column A, so both sides are numbers. The #N/A article lists this as one of six causes.

When VALUE gives #VALUE!

Then the text holds something that is not part of a number. =VALUE("36 EUR") gives #VALUE!, because of the word.

The other usual cause is a non-breaking space from a web page. TRIM removes normal spaces, but not this one. The #VALUE! article shows how to find it and remove it.

When a number should stay text

Some values look like numbers but are really codes. Converting them does damage.

• Codes with a leading zero. =VALUE("00741") gives 741. The zeros are gone for good.
• Long IDs. Excel keeps only 15 digits of a number. A 16-digit ID loses its last digit, which becomes 0.
• Phone numbers. You never add them up, so they gain nothing as numbers.

Keep these as text. Then make sure the other side of any lookup is text too.

Questions people ask

Why does my SUM show 0?

Every cell in the range is text. SUM finds no numbers, so the total is 0. Use one of the four fixes above.

How do I hide the green triangles without changing anything?

Select the cells and click the warning button. Choose Ignore Error. Do this only for codes that should stay text.

Why does a plus sign work when SUM does not?

The plus sign tries to turn text into a number first. SUM does not try; it skips text in a range. So a column of plus signs can hide the problem that SUM shows.

The lessons below practise VALUE, TRIM and ISNUMBER. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.