No Ticket Needed

Appearance

5 min fix 12 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Select the column, open the Data tab, click Text to Columns, and click Finish on the first screen. Every cell is re-read as a number and =SUM() starts working.

How to Fix Numbers Stored as Text in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016 and later
Article Type: Troubleshooting
Last Updated: 2026-07-29

Summary ​

Green triangles in the corner of your cells, =SUM() returning 0, and a VLOOKUP that cannot find a value sitting right there on screen all come from the same cause: Excel is holding those numbers as text. This article shows you how to confirm it, four ways to convert the values back, how to strip the invisible characters that block conversion, and how to fix a lookup where one side is text and the other is a real number.

Symptoms ​

  • =SUM() over a column of figures returns 0, or skips some rows and adds only the others.
  • A small green triangle sits in the top-left corner of each cell.
  • The numbers sit against the left edge of their cells. Real numbers align right on their own.
  • VLOOKUP and XLOOKUP return #N/A for values you can see in the lookup table.
  • SUMIF and COUNTIF return 0 for criteria that plainly match rows on screen.
  • Sorting the column puts 10 before 2, because it is sorting alphabetically.
  • A PivotTable offers Count rather than Sum as the default for the column.
  • Selecting the cells shows nothing on the status bar at the bottom right — no Sum, no Average, only Count.
  • Setting the cell format to Number changes nothing until you retype the value.

Prerequisites ​

  • Edit permission on the workbook.
  • A copy of the original file, or the ability to re-export it, if the column also holds codes that must keep their leading zeros.
  • No admin rights required.

Instructions ​

1. Confirm the Numbers Really Are Text ​

Two seconds of checking stops you converting a column of ZIP codes that were correct as they were.

  1. Click a single cell in the problem column.
  2. Look at where the value sits. Text hugs the left edge; numbers and dates line up on the right. Excel does this on its own, before any formatting is applied.
  3. Look at the Number Format dropdown in the Number group of the Home tab. If it reads Text, the cell format is the cause.
  4. Select several cells and read the status bar along the bottom of the window. Real numbers give you Average, Count and Sum. Text gives you Count alone.
  5. For a definitive answer, click an empty cell and enter:
=ISNUMBER(A2)

FALSE means the cell holds text. TRUE means the value is a genuine number and the problem lies somewhere else in the formula.

Note: The green triangle is Excel's error-checking flag, not the problem itself. Someone may have cleared the triangles with Ignore Error while leaving the text values in place, so a clean-looking column can still break =SUM(). Alignment and ISNUMBER are the reliable tests.

Note: Before converting, decide whether the column is meant to be a number at all. Order numbers, ZIP codes, account numbers and part codes belong as text — converting 02134 produces 2134, and anything over 15 digits loses its trailing digits permanently.

2. Use the Warning Icon's Convert to Number ​

The quickest fix, and it scales further than most people realise.

  1. Click the cell with the green triangle. A small yellow diamond carrying an exclamation mark appears immediately to the left of the cell.
  2. Click the diamond. A menu opens with a greyed heading at the top reading Number Stored as Text.
  3. Click Convert to Number. The triangle disappears and the value jumps to the right edge of the cell.

To do a whole column in one go:

  1. Click the first cell of the range.
  2. Press Ctrl + Shift + Down arrow to select down to the last filled cell. The yellow diamond stays visible beside the first cell.
  3. Click the diamond and choose Convert to Number. Every selected cell is converted at once.

Verification: Select the range again and read the status bar at the bottom right. Sum and Average now appear alongside Count.

Note: On selections of several thousand rows this method runs cell by cell and takes a noticeable pause. Step 3 handles large columns in a fraction of the time.

Note: Excel for the web does not offer Convert to Number on the warning menu. Use the VALUE formula in Step 5 instead, or open the workbook in the desktop app.

Tip: If the values are correct as text and the triangles are only visual clutter, choose Ignore Error from the same menu. To stop Excel flagging them at all, go to File > Options > Formulas, find the Error checking rules section, and clear Numbers formatted as text or preceded by an apostrophe. That hides the flag across every workbook on this computer — the values themselves are unchanged.

3. Convert a Whole Column with Text to Columns ​

This is the bulk fix. It sounds like the wrong tool, and it is the fastest one available.

  1. Click the column letter at the top of the sheet to select the entire column. Text to Columns works on one column at a time.
  2. Open the Data tab.
  3. In the Data Tools group in the middle of the ribbon, click Text to Columns. The Convert Text to Columns Wizard opens at step 1 of 3.
  4. Click Finish at the bottom of the dialog. Ignore the delimiter and format screens entirely.

The dialog closes and every value in the column shifts to the right edge of its cells. Excel re-read each cell as though you had retyped it, which is exactly what was needed.

Note: If the cells were formatted as Text, set the format first or the conversion will not stick. Select the column, open the Home tab, and set the Number Format dropdown to General. Then run Text to Columns. Changing the format alone does nothing to values already entered — that is why both actions are needed.

Note: Text to Columns overwrites the column to its right if it finds a delimiter in the data. Clicking Finish on screen 1 keeps the default of one column, but check that the Data preview panel at the bottom of the dialog shows a single unbroken column before you click.

Tip: For a column of values with a currency symbol or thousands separator baked in, use screen 3 of the wizard rather than clicking Finish. Click Next twice, click Advanced..., set Decimal separator and Thousands separator to match the data, and click OK, then Finish.

4. Multiply by 1 with Paste Special ​

Use this when Text to Columns is unavailable — on a protected sheet region, across several columns at once, or inside a table where you want the conversion in place.

  1. Click an empty cell somewhere off to the side and type 1. Press Enter.
  2. Click that cell again and press Ctrl + C.
  3. Select the range of text numbers you want converted. This can span several columns.
  4. Set the format first: on the Home tab, open the Number Format dropdown and choose General.
  5. Press Ctrl + Alt + V to open the Paste Special dialog. On a Mac, press Control + Command + V.
  6. Under Paste, select Values. Under Operation, select Multiply.
  7. Click OK. Every value in the range is multiplied by 1, which forces Excel to evaluate it as a number, and the results align right.
  8. Delete the helper cell holding the 1.

Note: Choosing Values under Paste keeps the formatting already on the target cells. Selecting All instead copies the helper cell's formatting over your range and flattens any colouring you had.

5. Clean Stubborn Values with TRIM, CLEAN and VALUE ​

When the column resists every method above, something invisible is attached to each value. This is normal for data copied out of a web page, a PDF, or a mainframe report.

  1. Insert a blank column to the right of the problem column.
  2. In its first cell, enter the formula that matches your situation:

For plain text numbers:

=VALUE(A2)

For values with leading or trailing spaces:

=VALUE(TRIM(A2))

For values copied from a web page or PDF, which often carry non-breaking spaces and non-printing characters:

=VALUE(SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160),""))
  1. Press Enter. A number appears, aligned right.
  2. Double-click the small square at the bottom-right corner of the cell to copy the formula down the whole column.
  3. Select the new column and press Ctrl + C.
  4. Right-click the first cell of the original column and click the Values icon under Paste Options — the clipboard with 123 on it.
  5. Delete the helper column.

Each function does one job: TRIM removes ordinary spaces from the ends, CLEAN removes non-printing control characters, SUBSTITUTE with CHAR(160) removes the non-breaking space that web pages use and that TRIM cannot touch, and VALUE converts what is left into a number.

Tip: To find non-breaking spaces without a formula, press Ctrl + H to open Find and Replace. Click into the Find what box, hold Alt, and type 0160 on the numeric keypad — the number pad on the right of the keyboard, not the row above the letters. Leave Replace with empty and click Replace All. Excel reports how many it removed.

Note: #VALUE! from these formulas means the cell holds something that is not a number at all — a currency code such as USD 1,200, a trailing minus from a mainframe export such as 1200-, or the text n/a. Strip the offending characters with SUBSTITUTE first, for example =VALUE(SUBSTITUTE(A2,"USD ","")). For the wider family of formula errors, see How to Fix Common Excel Formula Errors.

6. Fix a Lookup That Fails Because One Side Is Text ​

VLOOKUP returns #N/A when the types do not match, even though the values look identical on screen. The number 1045 and the text 1045 are different things to Excel.

  1. Work out which side is text. Click the lookup value and check its alignment, then do the same on the first column of the lookup table. The one hugging the left edge is the text.
  2. Confirm with =ISNUMBER() on both cells, as in Step 1.
  3. Fix the column, not the formula, wherever you can. Convert whichever side is text using Step 3, and the lookup starts working with no formula change.

When the column cannot be converted — a shared file, a refreshed query, a protected sheet — force the match inside the formula instead.

When your lookup value is text and the table holds real numbers, wrap the lookup value:

=VLOOKUP(VALUE(A2),Products,2,FALSE)

When your lookup value is a number and the table holds text, append an empty string to turn it into text:

=VLOOKUP(A2&"",Products,2,FALSE)

To cover both without knowing which is which, use IFERROR to try one and fall back to the other:

=IFERROR(VLOOKUP(A2,Products,2,FALSE),VLOOKUP(A2&"",Products,2,FALSE))

Note: The same mismatch breaks SUMIF, COUNTIF, MATCH and XLOOKUP, and it breaks them silently — you get 0 or #N/A rather than a warning. When a criteria formula returns a suspiciously round 0, check the data types on both sides before rewriting the formula. See How to Use VLOOKUP in Excel for the full lookup syntax.

Tip: Prevent the whole class of problem at the source. When the data arrives as a CSV, import it with Data > From Text/CSV and set each column's type deliberately rather than letting Excel guess — see How to Open a CSV File in Excel Without Breaking It.

Troubleshooting ​

WARNING

Converting a whole column to numbers destroys leading zeros and truncates values longer than 15 digits. 02134 becomes 2134 and a sixteen-digit account number loses its final digits for good. Check what the column actually holds before you convert it — ZIP codes, phone numbers, account numbers and part codes are meant to be text, and the green triangle on those cells is a false alarm you clear with Ignore Error.

Symptom / ErrorPotential CauseSolution
=SUM() returns 0Text values are skipped by SUMSelect the column and run Data > Text to Columns > Finish (Step 3).
Numbers stay left-aligned after changing the format to NumberFormat changes do not re-enter valuesSet the format to General, then force a re-read with Text to Columns (Step 3) or the multiply-by-1 paste (Step 4).
Convert to Number is missing from the warning menuDifferent error type, or Excel for the webRead the greyed heading at the top of the menu. If it does not say Number Stored as Text, the cell holds a different problem. In Excel for the web, use the VALUE formula (Step 5).
Text to Columns is greyed outMore than one column selected, or the sheet is protectedSelect a single column. If the sheet is protected, use the multiply-by-1 paste instead (Step 4).
VLOOKUP still returns #N/A after convertingTrailing or non-breaking spaces remainClean the values with TRIM, CLEAN and CHAR(160) before converting (Step 5).
The column reverts to text every time the data refreshesThe query declares the column as textFix it at the import: edit the query and set the column type there. See How to Open a CSV File in Excel Without Breaking It.