Fixes / Excel · Checked

Excel "Number Stored as Text": what it means and how to fix it

Short answer

Excel is warning that a cell holds a number saved as text. SUM, sorting and lookups treat it as a word, not a value, so totals can come out wrong. The quickest fix is to select the flagged cells, click the yellow warning icon next to the selection (Alt+Shift+F10 on Windows) and choose Convert to Number. The green triangles should disappear and the numbers will move to the right side of the cell.

What this message means

Excel stores every cell as either a value (number, date) or text. This warning comes from the error-checking rule "Numbers formatted as text or preceded by an apostrophe". It marks a cell that looks like a number but is stored as text, and shows a small green triangle in the cell's top-left corner. Nothing is corrupted. The catch is that text numbers are skipped by SUM and AVERAGE, sort in the wrong order (10 before 2), and fail to match in VLOOKUP, XLOOKUP and MATCH when the other side is a real number. Text numbers are also usually left-aligned, which is a quick visual clue.

Common causes

CauseHow to tell
Data was imported or pasted from another source (CSV, accounting or ERP export, a web page, a PDF, another app)A whole column shows green triangles right after you paste or open the file, and the numbers sit on the left side of the cells.
The cell was formatted as Text before the number was typedHome > Number Format shows "Text" for the cell. Changing it to General or Number leaves the green triangle in place until you re-enter the value.
The number starts with an apostrophe (')Click the cell and look at the formula bar: it shows '123 even though the cell shows 123.
Hidden spaces or nonprinting characters around the number, often non-breaking spaces from web pagesConvert to Number is missing or does nothing. =LEN(A2) returns more characters than you can see, and =ISNUMBER(A2) still returns FALSE.
The decimal or thousands separator doesn't match your regional settings (for example 1.234,56 on a US-English system)Only values with decimals or thousands separators stay as text, while whole numbers like 500 convert fine.
A formula returns text, for example TEXT(), LEFT(), MID(), or joining values with &The cell contains a formula, and ISNUMBER on its result returns FALSE even though it looks like a number.

How to fix it

1. Use Convert to Number from the warning icon

  1. Select the cells with the green triangles. You can select a whole column, but make sure the first cell you select is one of the flagged cells.
  2. Click the yellow diamond warning icon that appears beside the selection. On Windows you can also press Alt+Shift+F10.
  3. Choose Convert to Number.
  4. Check the result: the numbers should now be right-aligned, the triangles gone, and the SUM in the status bar at the bottom should include them.

2. Convert a whole column with Text to Columns

  1. Select one column of flagged cells (Text to Columns works on one column at a time).
  2. Go to the Data tab and click Text to Columns.
  3. Click Finish right away. You don't need to change any options.
  4. If the numbers use a different decimal separator than your system, click Next twice instead, then click Advanced and set the Decimal and Thousands separators to match the data before clicking Finish.

3. Multiply by 1 with Paste Special

  1. Type 1 in an empty cell (formatted as General) and press Enter.
  2. Select that cell and copy it (Ctrl+C on Windows, Cmd+C on Mac).
  3. Select the text-number cells, right-click and choose Paste Special.
  4. Under Operation choose Multiply, then click OK.
  5. Delete the helper cell with the 1. If the cells still show as text, set their format to General or Number.

4. Fix cells formatted as Text before the numbers were typed

  1. Select the cells, then on the Home tab open the Number Format box and choose General or Number.
  2. Changing the format alone does not convert values already typed. Re-enter each one by selecting the cell, pressing F2 (on a Mac without function keys, Ctrl+U), then Enter.
  3. For many cells, run Text to Columns > Finish (fix 2) after changing the format instead of re-entering each one.
  4. To keep new entries as numbers, make sure the column is not set to Text before you type or paste.

5. Strip hidden spaces and characters with a formula

  1. Insert an empty column next to the problem data.
  2. In the first row enter =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),"")))), replacing A2 with your first data cell. This removes regular spaces, non-breaking spaces and nonprinting characters.
  3. If the data uses commas as decimals (1.234,56), use =NUMBERVALUE(A2,",",".") instead.
  4. Drag the fill handle (the small square at the cell's bottom-right corner) down to fill the rest of the column.
  5. Copy the new column, then paste it over the original with Paste Special > Values, and delete the helper column.

6. Hide the warning when the text is intentional

Windows: File > Options > Formulas. Mac: Excel menu > Settings (Preferences in older versions) > Error Checking.

  1. For individual cells that should stay text (ZIP codes, phone numbers, IDs with leading zeros), select them, click the warning icon and choose Ignore Error.
  2. To turn the check off for all workbooks on Windows, go to File > Options > Formulas and clear Numbers formatted as text or preceded by an apostrophe under Error checking rules.
  3. On a Mac, go to Excel > Settings > Error Checking and clear Numbers formatted as text.
  4. To bring back warnings you ignored earlier, click Reset Ignored Errors on the same screen.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If none of these work, test one cell with =ISNUMBER(A2) and =LEN(A2) to see whether the value is still text and whether it has extra characters. =CODE(RIGHT(A2)) shows the code of the last character, which exposes odd characters that TRIM misses. If the data comes from a system export, ask whoever runs that system to export numbers without quotes, currency symbols or thousands separators, or bring the file in with Data > From Text/CSV (Get & Transform) and set the column type to Decimal Number. If the workbook is shared or protected and the options are greyed out, ask the file owner or your IT administrator to unprotect the sheet.

Questions people ask

Why doesn't changing the cell format to Number fix it?

A number format only changes how a cell's value is displayed. It does not convert text that is already stored there. After changing the format, re-enter the value (F2 then Enter) or use Convert to Number, Text to Columns or Paste Special Multiply.

Why is Convert to Number missing or not working?

The cell usually contains hidden characters such as non-breaking spaces, or a decimal separator Excel doesn't recognise, so Excel can't read it as a number. Clean it with =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),"")))) or use NUMBERVALUE for other separators. Also check that background error checking is turned on under File > Options > Formulas.

How do I keep leading zeros without the green triangle?

Leave the cells as text and choose Ignore Error, or use a custom number format such as 00000 so the value stays numeric but displays the zeros. Use text for IDs you will never calculate with, like ZIP codes or account numbers.

Why does my SUM or VLOOKUP give the wrong result?

SUM skips text numbers, and lookups treat the text "123" and the number 123 as different values, so they return #N/A or a low total. Convert the lookup column and the lookup value to the same type.

Can I convert a whole sheet at once?

Select every flagged cell (you can Ctrl-click to add ranges on Windows or Cmd-click on Mac), start the selection on a flagged cell, then use the warning icon's Convert to Number. For very large ranges, Paste Special Multiply is faster and works on several columns at once.

Sources

Related errors