Excel Numbers Stored as Text: 6 Ways to Convert Them

Your SUM shows 0, your VLOOKUP returns #N/A, and sorting puts 100 before 20. The cause is often the same: numbers stored as text.

They look like numbers, but Excel treats them as words. Here’s how to spot them and six ways to fix them.

How to spot numbers stored as text

Left-aligned: real numbers align to the right by default; text aligns left.
Green triangle: a small green triangle appears in the top-left corner of the cell.
Leading apostrophe: the formula bar shows '1001.
SUM ignores them: the status bar shows a Count but no Sum when you select the cells.
ISTEXT test: =ISTEXT(A2) returns TRUE.

Why it happens

Data exported from accounting software, websites or databases.
Cells formatted as Text before the numbers were typed.
Numbers copied with spaces or invisible characters.
An apostrophe typed in front of the number.

Method 1: Convert to Number (quickest)

Select the cells with green triangles.
Click the yellow warning icon that appears next to the selection.
Choose Convert to Number.

This works well for a few hundred rows. On very large ranges it can be slow, so use Method 2.

Method 2: Text to Columns (best for whole columns)

Select the column.
Go to Data → Text to Columns.
Click Finish straight away.

Excel re-enters every value and converts text numbers into real numbers. It’s fast, even on thousands of rows.

Method 3: Paste Special Multiply

Type 1 in an empty cell and copy it.
Select the text numbers.
Right-click → Paste Special → choose Multiply → OK.

Multiplying by 1 forces Excel to treat each value as a number. Delete the helper cell afterwards.

Method 4: The VALUE function

Use a formula in a helper column:

=VALUE(A2)

Copy the results and paste them back as values (Paste Special → Values). A shorter trick that does the same job is =A2*1 or =--A2.

Method 5: Remove spaces first

If the methods above don’t work, the cells probably contain spaces or non-breaking spaces copied from a website. Clean them first:

=VALUE(TRIM(SUBSTITUTE(A2, CHAR(160), "")))

SUBSTITUTE removes non-breaking spaces (character 160) and TRIM removes normal extra spaces.

Method 6: Change the cell format and re-enter

If cells are formatted as Text, changing the format to General doesn’t convert existing values on its own. After changing the format, use Method 2 (Text to Columns) to re-enter them, or press F2 and Enter on each cell for small ranges.

Fixing it inside a lookup formula

Sometimes you can’t change the source data. Convert inside the formula instead.

Lookup value is text, the list holds numbers:

=XLOOKUP(VALUE(E2), A2:A100, C2:C100)

Lookup value is a number, the list holds text:

=XLOOKUP(E2&"", A2:A100, C2:C100)

For more lookup troubleshooting, read How to Fix #N/A in VLOOKUP and XLOOKUP.

When you want numbers stored as text

Some values look like numbers but should stay as text: phone numbers, ZIP or postal codes with leading zeros, ID numbers, and credit-card-style codes. Converting “00123” to a number turns it into 123. For these, keep the column formatted as Text, and tell Excel to ignore the warning: click the yellow icon and choose Ignore Error.

In Google Sheets

Google Sheets doesn’t show green triangles. Select the cells and choose Format → Number → Number, or use =VALUE(A2) in a helper column. If that fails, the values likely contain spaces; use the TRIM and SUBSTITUTE formula above.

Summary

Text numbers are left-aligned and show a green triangle.
Use Convert to Number for small ranges and Text to Columns for large ones.
Clean spaces with TRIM and SUBSTITUTE when nothing else works.
Keep codes with leading zeros as text on purpose.