How To Convert Numbers Stored As Text In Excel (2026)

Numbers stored as text look normal on screen, but Excel will not add them up, sort them correctly or match them in a lookup.

The usual sign is a number that hugs the left side of its cell, often with a small green triangle in the corner.

This guide shows the conversion methods on Microsoft’s support pages, from a one-click fix to formulas for whole columns.

Quick fix: Convert to Number

This is the fastest method when the green triangle is showing.

  1. Select the affected cells.
  2. Select the error indicator in the top-left corner of the selection, or press Alt+Shift+F10.
  3. Choose Convert to Number from the menu.

The values become right-aligned numbers and the green triangle disappears.

How to tell a number is stored as text

Microsoft lists the same few signs.

  • The value is left-aligned instead of right-aligned in the cell.
  • A small green triangle sits in the upper-left corner of the cell.
  • Sums, sorting or lookups give odd results.

The problem often appears after you import data, copy it from another source, or type numbers into cells already formatted as text.

Microsoft notes that text-formatted numbers can cause calculation problems and confusing sort orders.

Which method should you use?

SituationMethod
A few cells with the green triangleConvert to Number
A whole column with no trianglePaste Special with Multiply, or Format Cells then Text to Columns
You want to keep the original columnVALUE or NUMBERVALUE in a helper column
Numbers in another country’s format, such as 2.500,27NUMBERVALUE with separators
Negative numbers ending in a minus signLEFT and LEN formula multiplied by -1
Text with stray spacesTRIM or CLEAN first

If the green triangle does not appear

The indicator depends on background error checking being on.

  1. Go to File > Options.
  2. Select Formulas.
  3. Under Error Checking, tick Enable background error checking.

Convert a whole column with Paste Special

This method changes the numbers in place without a helper column.

  1. Select a blank cell and confirm its number format is General on the Home tab.
  2. Type 1 in that cell and press Enter.
  3. Select the cell and press Ctrl+C.
  4. Select the text-formatted numbers.
  5. On the Home tab, open the arrow below Paste and choose Paste Special.
  6. Under Operation, select Multiply and select OK.
  7. Delete the helper cell with the 1 in it.

The values look the same, but Excel now stores them as numbers.

Microsoft documents this method on an older Excel 2013 page, so check the result on your version.

Convert with the VALUE function

  1. Insert a new column next to the column that holds the text numbers.
  2. In the first cell of the new column, type =VALUE(, click the text cell and close the bracket.
  3. Drag the fill handle down to copy the formula.
  4. To replace the originals, copy the formula cells, select the first original cell, and use Paste > Paste Special > Values (Ctrl+Shift+V).

Use NUMBERVALUE for different separators

NUMBERVALUE converts text to a number in a way that does not depend on your locale.

Its syntax is =NUMBERVALUE(Text, [Decimal_separator], [Group_separator]).

FormulaResult
=NUMBERVALUE("2.500,27",",",".")2500.27
=NUMBERVALUE("3.5%")0.035
=NUMBERVALUE(" 3 000 ")3000, because spaces are ignored

An empty string returns 0, and a repeated decimal separator returns #VALUE!.

If you leave out the separators, Excel uses those of the current locale.

Format a whole column as numbers

Microsoft’s #N/A page gives this route for a full column.

  1. Select the cells and press Ctrl+1 to open Format Cells.
  2. Choose the number format you want.
  3. To apply it to the entire column, select the column, then go to Data > Text to Columns and select Finish.

You can also choose a format from the Number group on the Home tab by opening its Dialog Box Launcher.

Fix negative numbers with a trailing minus sign

Some accounting exports write a negative number as text with the minus sign on the right, such as 156-.

If that text is in A2, use =LEFT(A2,LEN(A2)-1)*-1, which returns -156.

Remove spaces and hidden characters first

Copied or imported data often carries extra spaces or nonprinting characters.

TRIM removes extra spaces and keeps single spaces between words.

CLEAN removes nonprinting characters.

Run these on the data before you apply a number format.

Where text numbers cause problems

WhereWhat goes wrong
PivotTablesA value field shows COUNT instead of SUM when Excel reads the data as text
VLOOKUP and XLOOKUPA number on one side and text on the other do not match and return #N/A
SortingText sorts in a different order from numbers

For lookups, make both sides the same data type.

Find which cells are text and fix #VALUE! errors

Enter =ISTEXT(A2) in a helper column, and TRUE means the cell holds text.

ISTEXT only identifies the cell, so you still have to convert it.

Microsoft says the + – * / operators fail when a referenced cell contains text.

A function such as SUM ignores text, so =SUM(A2:C2) can replace =A2+B2+C2, but it skips the text cells instead of fixing them.

Hidden spaces can also trigger the error, and Find and Replace with a single space in Find what and nothing in Replace with removes them from a selected column.

When you want numbers to stay as text

Some values should never be converted, such as long codes and ID numbers.

Excel keeps only 15 digits of precision, so digits after the 15th are rounded down to zero unless the cells are formatted as text.

By default Excel also drops a leading zero, so 0784367998 becomes 784367998.

To keep a leading zero, format the cells as Text in the Number group on the Home tab, or use a custom number format such as 0000000000.

Format empty cells as Text first, and numbers typed into them afterward are stored as text.

Turn off the green triangle

You can switch the warning off if you do not want to see it.

  1. Select File > Options.
  2. Select Formulas.
  3. Under error checking rules, clear Numbers formatted as text or preceded by an apostrophe.
  4. Select OK.

Turning the rule off hides the warning but does not convert anything.

To clear the indicator on selected cells only, choose Ignore Error from the indicator menu.

Lookup problems are covered in our guide to XLOOKUP and VLOOKUP.

See why Excel formulas are not calculating if a formula shows as text.

For other error codes, read fixing #N/A, #VALUE! and #REF! errors.

Sources

Leave a Reply

Your email address will not be published. Required fields are marked *