How to Convert Text to Number in Excel — 5 Easy Methods

One of the most frustrating Excel problems is when numbers are stored as text. Your SUM formula returns 0. Your VLOOKUP fails to match. Sorting does not work correctly. This guide covers every reliable method to convert text to number in Excel, from the fastest one-click fix to formulas that work for bulk conversions.

How to Tell If Numbers Are Stored as Text

Look for these signs:

• A green triangle in the top-left corner of the cell
• Numbers are left-aligned in the cell (numbers are right-aligned by default)
• SUM or AVERAGE formulas return 0 instead of the expected total
• The cell's format shows "Text" in the Home → Number Format dropdown

Method 1: The Green Triangle Fix (Fastest)

This works when Excel detects the problem itself and shows the green triangle indicator:

1. Select the cells with the green triangle.
2. A small yellow warning icon (⚠️) appears to the left of the selection.
3. Click the warning icon.
4. Choose "Convert to Number" from the dropdown menu.

Done. The numbers are now real numbers and formulas will calculate correctly.

Method 2: Multiply by 1 Using Paste Special

This is a reliable method when the green triangle does not appear:

1. Type 1 in any empty cell and copy it (Ctrl+C).
2. Select all the text-formatted number cells you want to convert.
3. Right-click → Paste Special (or press Ctrl+Alt+V).
4. Select Values under Paste, and Multiply under Operation.
5. Click OK.

Multiplying by 1 forces Excel to interpret the cell contents as numbers.

Method 3: The VALUE() Formula

Use this when you want to keep the original data and get converted numbers in a separate column:

• In a new cell, type: =VALUE(A1) where A1 contains the text-formatted number.
• Press Enter. The result is a real number.
• Copy the formula down for all rows.
• If you want to replace the originals: copy the new column → Paste Special → Values only → paste over the original column.

Method 4: Text to Columns (No Formula Needed)

This method forces Excel to re-parse the cell data:

1. Select the column of text-formatted numbers.
2. Go to the Data tab → click Text to Columns.
3. Leave all settings as default (Delimited, comma, etc.).
4. Click Finish immediately without changing anything.

Excel re-processes the column and recognises the numbers correctly.

Method 5: Change the Cell Format First

This works best for freshly entered data:

1. Select the cells.
2. In the Home tab, change the format dropdown from "Text" to "Number" or "General."
3. Press F2 then Enter in each cell to reconfirm the value.

Note: For large datasets, this is tedious. Use Methods 2 or 4 instead.

Why This Happens: Common Causes

• Imported CSV data — many data exports format number columns as text
• Copied from websites or PDFs — browsers and PDF text extractors often format numbers with invisible characters
• Leading apostrophe — typing '123 in Excel forces text formatting
• Cells pre-formatted as Text before numbers were entered