The fastest way to convert text to numbers in Excel

If a column shows numbers but Excel treats them as text — they're left-aligned instead of right-aligned, formulas won't add them up, or you see a green triangle warning — you have three working methods. The Find & Replace method works on any version and takes 30 seconds. The VALUE function works if you need the numbers in a new column. The Text to Columns feature converts in place but requires a few more clicks.

The reason this happens is that Excel distinguishes between the number 5 and the text "5". When data comes from another program, a web download, or manual entry with a leading apostrophe, Excel stores it as text. Calculations skip text values, and sorting puts them in alphabetical order instead of numerical order.

Key Takeaways

  • Find & Replace with a blank search converts text numbers to real numbers in seconds and works on any Excel version.
  • The VALUE function creates a formula that converts text to numbers, useful when you want to keep the original column unchanged.
  • Text to Columns converts text numbers in place by running them through Excel's data import process.
  • After conversion, check that numbers are right-aligned and formulas now include them in calculations.

Using Find & Replace to convert text numbers

Open the column with text numbers. Press Ctrl+H (or Cmd+H on Mac) to open Find & Replace. Leave the Find field empty. Leave the Replace field empty. Click Replace All. Excel will reprocess every cell and convert text numbers to actual numbers.

This works because Find & Replace forces Excel to re-evaluate each cell's contents. When you replace nothing with nothing, Excel still re-enters the data, and this time it recognizes the numbers as numbers instead of text. The green warning triangle disappears, and numbers move to the right side of the cell.

If you want to be more cautious, select only the cells you want to convert before opening Find & Replace. This limits the change to that range instead of the entire sheet.

Using the VALUE function to convert in a new column

If you want to keep the original text column and put numbers in a new column, use the VALUE function. In the cell next to your first text number, type =VALUE(A1) where A1 is the cell with the text number. Press Enter. The result is a real number.

Copy this formula down the entire column by clicking the cell with the formula, then double-clicking the small square at the bottom-right corner of the cell. Excel fills the formula down to match the length of your data. You now have a column of real numbers.

If you want to replace the original text column, copy the new number column, then right-click and choose Paste Special. Click Values only, then click OK. This pastes only the numbers, not the formulas. Delete the original text column if you no longer need it.

Using Text to Columns to convert in place

Select the column with text numbers. Go to the Data tab at the top and click Text to Columns. A wizard opens. Click Next on the first screen (Delimiters). Click Next again on the second screen (Column data format). On the third screen, make sure the column is set to General format, then click Finish.

Excel processes the column as if it were importing data and converts text numbers to real numbers in place. This is faster than Find & Replace if you have a very large column, though the difference is small for most sheets.

Text to Columns works best when your text numbers are simple — just digits, no extra spaces or symbols. If your data has leading spaces or unusual formatting, clean those up first or use Find & Replace instead.

Checking that the conversion worked

After conversion, look at the alignment. Real numbers are right-aligned by default; text is left-aligned. If your numbers are now on the right side of their cells, the conversion succeeded. If they're still on the left, they're still text.

Test a formula. In an empty cell, type =SUM(A1:A10) where A1:A10 is your converted range. If the formula returns a total, the numbers are real. If it returns 0 or an error, they're still text and the conversion did not work.

Why text numbers happen and how to prevent them

Text numbers usually come from data pasted from a website, a PDF, or another program that doesn't send number formatting. Sometimes they come from a CSV file opened in the wrong way. Excel can also create them if you type an apostrophe before a number — this forces Excel to treat it as text.

To prevent this when pasting data, use Paste Special instead of regular paste. Right-click and choose Paste Special, then click Values. This strips formatting and often lets Excel recognize numbers as numbers. When opening a CSV file, use File > Open instead of double-clicking, so you can choose the column format during import.

Frequently Asked Questions

Why does Excel show a green triangle on my numbers?

The green triangle is Excel's warning that a cell contains text that looks like a number. This happens when the cell is formatted as text or the data came in as text. Use Find & Replace or Text to Columns to convert it. The warning disappears once the cell contains a real number.

Can I convert text numbers that have commas or dollar signs?

Yes, but you need to remove the extra characters first. Use Find & Replace to search for $ and replace with nothing, then search for , and replace with nothing. After the text is just digits, use any of the three methods above to convert to numbers.

What if VALUE returns an error?

VALUE returns #VALUE! when the cell contains text that is not a number — like "5a" or "five". Check the cell for extra characters, spaces, or letters. If the cell truly contains only digits, the text may have invisible characters. Use Find & Replace to clean it, then try VALUE again.

Does Find & Replace work on formulas or just text?

Find & Replace works on text numbers, not on formulas. If a cell contains a formula like =A1+5, Find & Replace will not convert it. You must edit the formula itself or use a different approach to convert the cells that formula references.

Can I undo a conversion if I change my mind?

Yes. Press Ctrl+Z immediately after the conversion to undo it. If you closed the file, you cannot undo. This is why it's safe to use Find & Replace — you can always reverse it if something goes wrong.