Cellwise Try it free

Excel guide · 4 min read

How to convert numbers stored as text in Excel

Quick answerSelect the cells, click the yellow warning icon that appears, and choose Convert to Number. For a whole column, use Data > Text to Columns > Finish. In a formula, wrap the cell in VALUE() or put -- in front of it. You can tell a number is stored as text when it sits on the left of the cell, shows a small green triangle, or SUM leaves it out.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1InvoiceAmount
2INV-3011240
3INV-302865
4INV-3032310
5INV-304$1,045.50
6INV-305 590

Why it matters: SUM silently skips text

This invoice column came from an export. Three of the five amounts are text: "865", "$1,045.50" and " 590 " with spaces around it. They look like numbers, but SUM ignores text in a range, so the total is quietly wrong. Nothing turns red and there is no error.

The total below only adds the two real numbers, 1,240 and 2,310.

=SUM(B2:B6)

Result: 3550

Only 3,550, because the three text amounts are skipped. The real total is 6,050.50.

Find the text cells fast

COUNT counts numbers only, while COUNTA counts anything that is not empty. If COUNT is lower than COUNTA on a column that should be all numbers, some cells are text. To list them, add a helper column with =ISTEXT(B2) and filter for TRUE.

Other giveaways: the value is left-aligned when the rest of the column is right-aligned, there is a green triangle in the corner, or the status bar shows a Count but no Sum when you select the cells.

=COUNTA(B2:B6)-COUNT(B2:B6)

Result: 3

5 filled cells minus 2 numbers: 3 amounts are stored as text.

Fix 1: Convert to Number (quickest for a few cells)

Excel flags most numbers stored as text with a green triangle. Selecting them shows a yellow warning icon with a one-click fix. It works on any selection size, but Excel only offers it when the first selected cell has the triangle.

  1. Select the cells (start the selection on a cell with a green triangle).
  2. Click the yellow warning icon next to the selection.
  3. Choose Convert to Number.

Fix 2: Text to Columns (best for a whole column)

This old trick re-enters every cell in a column, which turns text that looks like a number into a real number. It also handles cells formatted as Text, where Convert to Number sometimes does not appear.

It works on one column at a time and changes the cells in place, so there is nothing to copy back.

  1. Select the column.
  2. Go to Data > Text to Columns.
  3. Click Finish straight away (the defaults are fine).
  4. If the cells were formatted as Text, set them to General first: Home > Number Format > General.

Fix 3: VALUE or -- in a formula

When you cannot change the source (a linked export, a shared sheet), convert inside your formula. VALUE turns text into a number and understands currency signs and thousands separators. Two minus signs do the same thing in less typing: the first flips the sign and turns the text into a number, the second flips it back.

To total the whole column including the text amounts, convert every cell before adding. SUMPRODUCT handles the array in any version of Excel.

=SUMPRODUCT(--B2:B6)

Result: 6050.5

Every amount is converted first, so the total is the real 6,050.50.

Fix 4: Paste Special > Multiply

Multiplying by 1 forces Excel to treat each cell as a number. Paste Special lets you do it to a whole block in place, including several columns at once.

  1. Type 1 in an empty cell and copy it.
  2. Select the cells stored as text.
  3. Right-click > Paste Special, choose Multiply under Operation, click OK.
  4. Delete the 1.

Common mistakes

Only changing the number format to Number or General

Changing the format does not change what is stored. The cell stays text until it is re-entered. Use Text to Columns or Convert to Number afterwards.

Spaces copied from web pages

A non-breaking space (character 160) stops conversion. Use Find and Replace: click in the Find box, hold Alt and type 0160 on the number pad, leave Replace empty, and click Replace All. Or clean it in a formula with =SUBSTITUTE(B2,CHAR(160),"").

Converting IDs that should stay text

ZIP codes, phone numbers and product codes with leading zeros should be text. Converting 02134 to a number turns it into 2134.

Using SUM(VALUE(B2:B6)) in older Excel

In Excel 2019 and older it needs Ctrl+Shift+Enter. SUMPRODUCT(--B2:B6) works everywhere without that.

FAQ

Why does Excel store numbers as text?

Usually because the data was imported or pasted from another system, the cell was formatted as Text before typing, or the entry starts with an apostrophe. Spaces around the number also force text.

How do I convert a whole column of numbers stored as text?

Select the column and run Data > Text to Columns > Finish. It re-enters every cell and converts them all in one step.

How do I hide the green triangles without converting?

Select the cells, click the warning icon and choose Ignore Error. To turn the check off everywhere, go to File > Options > Formulas and untick Numbers formatted as text or preceded by an apostrophe.

Why does VLOOKUP fail on numbers stored as text?

A lookup compares exact values, and the number 865 is not equal to the text "865". Convert one side so both are numbers, or use --F2 or F2&"" inside the lookup.

Functions used

Related guides

Updated