Fix Numbers Stored as Text in Excel

How to Detect Numbers Stored as Text

Signal 1: Left alignment

Real numbers align right in a cell by default. Text aligns left. A column where some cells are left-aligned and others right-aligned almost always contains mixed types — the left-aligned ones are text.

Signal 2: Green triangle in the cell corner

Excel displays a small green triangle in the top-left of cells where it detects a number stored as text. Click the cell → a yellow warning icon appears with the message "Number Stored as Text". This is the clearest indicator.

Signal 3: ISNUMBER test

In a blank column, enter =ISNUMBER(A1). Copy it down the column. Any cell returning FALSE is text — including text-formatted numbers. This is the most reliable programmatic check and works even when the green triangle is not shown.

Signal 4: SUM returns less than expected

If =SUM(A1:A20) gives a lower result than manually adding a few of the cells, some cells in the range are text and being silently skipped. Check with ISNUMBER to find which ones.

Why Does This Happen?

• CSV exports from databases or web tools — numbers often come through as text strings
• Column pre-formatted as Text — any value typed into a Text-formatted cell becomes text, even if it is a number
• Leading apostrophe — typing '1000 forces text storage. The apostrophe is invisible in the cell
• Pasted from PDF, email, or web browser — external sources frequently produce text-typed numbers
• Imported via Power Query — if the column type was set to Text during import, numbers come in as text

5 Methods to Fix Numbers Stored as Text

Method 1: Convert to Number (Green Triangle — Fastest for Small Ranges)

1. Select the cells showing the green triangle warning
2. Click the yellow warning icon
3. Select Convert to Number

Excel converts all selected cells to real numbers instantly. This is the quickest method when the green triangle is visible.

Method 2: Paste Special Multiply (Best for Large Ranges)

1. Type 1 in any empty cell and copy it (Ctrl+C)
2. Select the full range of text-formatted numbers
3. Right-click → Paste Special
4. Select Multiply under Operation
5. Click OK

Multiplying by 1 forces Excel to re-evaluate each cell as a number. This works even without the green triangle and converts the entire range in one step.

Method 3: Text to Columns (Best for an Entire Column)

1. Select the column of text-formatted numbers
2. Go to Data → Text to Columns
3. Click Finish immediately (no other settings needed)

This forces Excel to re-parse all values in the column. Particularly effective for columns imported from CSV files.

Method 4: VALUE() Formula (Best When You Need to Keep Originals)

In a new adjacent column, enter =VALUE(A1) and copy it down to match your data range. This converts text to a real number in a separate column. Once done:

1. Copy the new column
2. Paste Special → Values only over the original column
3. Delete the helper column

Method 5: Change Format and Force Recalculation (For Decimal Numbers)

1. Select the cells
2. Change the cell format from Text to General (Home → Number Format dropdown)
3. Open Find & Replace (Ctrl+H)
4. Find: . Replace with: . (a period replaces a period)
5. Click Replace All

This forces Excel to re-evaluate each cell under the new General format. Only works reliably for cells containing decimal numbers.

Preventing This Problem in the Future

• Never pre-format data columns as "Text" unless you specifically need text (e.g. ID codes with leading zeros)
• When importing CSV data, use Data → From Text/CSV and explicitly set numeric columns to "Number" in the import wizard
• After pasting data from external sources, run the ISNUMBER check on any column used in calculations
• For ID codes that must preserve leading zeros, use a custom number format like 0000000 rather than formatting as Text — the value stays a real number but displays with the leading zeros

Frequently Asked Questions

My SUM is returning 0 for cells that show numbers. Is this the cause?

Almost certainly. Test with =ISNUMBER(A1) on one of the cells. If it returns FALSE, use Method 2 (Paste Special Multiply) to convert the range in one step.

VLOOKUP returns #N/A even though the value is clearly in the table.

This is the most common symptom of a data type mismatch. Your lookup value is a real number but the lookup column contains text numbers (or vice versa). Convert the lookup column to numbers using any method above, then retry the VLOOKUP.

Why does the green triangle sometimes not appear?

Excel's automatic detection of text-formatted numbers is not 100% reliable — it misses some cases, particularly when cells are formatted as Text from the start. Use the ISNUMBER test for a definitive answer, or use the ExcelErrorFinder audit tool which detects this at the cell type level regardless of the green triangle.