The fastest way to convert text numbers to real numbers

Excel treats text that looks like numbers differently from actual numbers — text numbers won't add up correctly, won't sort properly, and won't work in formulas. The quickest fix is to use the Text to Columns feature, which converts a column of text numbers to real numbers in seconds.

Select the column containing your text numbers, go to the Data tab, click Text to Columns, then click Finish on the dialog that appears. Excel will convert the entire column in one step. This works because Text to Columns forces Excel to re-read the data and recognize the numbers as numeric values instead of text.

Key Takeaways

  • Text to Columns is the fastest method — select your column, click Data > Text to Columns, then Finish.
  • You can also multiply by 1 using a helper column, then copy and paste the results back as values.
  • The VALUE function converts text numbers one cell at a time and works well for small batches.
  • Text numbers appear left-aligned in cells, while real numbers appear right-aligned — this is how to spot the problem.
  • After conversion, your numbers will sort correctly and formulas will calculate them properly.

Why Excel treats numbers as text in the first place

Numbers arrive as text when they're imported from another program, copied from a website, or entered after an apostrophe. Excel sees the apostrophe as an instruction to treat whatever follows as text, even if it's all digits. You won't see the apostrophe in the cell, but it's there in the formula bar if you click the cell.

The visual clue is alignment: text numbers sit on the left side of their cells, while real numbers sit on the right. If your numbers are left-aligned, they're text. This matters because a column of text numbers won't sum, won't average, and won't sort from smallest to largest — Excel will sort them alphabetically instead, so "9" comes before "10".

Using Text to Columns (the one-step method)

This is the method to use when you have a full column of text numbers and want the fastest result. Click the column header to select the entire column, or select just the cells containing text numbers. Then click the Data tab in the ribbon.

Click Text to Columns. A dialog box opens with three steps, but you don't need to change anything — just click Finish at the bottom right. Excel converts all the text numbers to real numbers and puts them back in the same cells. The entire process takes a few seconds.

If you only have a few cells to convert, this method still works, but the next two methods might feel faster because they don't open a dialog.

Using the multiply-by-1 method (the helper column approach)

This method works by creating a formula that forces Excel to do math with your text numbers, which automatically converts them. In an empty column next to your text numbers, type the formula =A1*1 (replace A1 with the first cell of your text numbers). Press Enter.

Click the cell with your formula, then drag the fill handle (the small square at the bottom right corner of the cell) down to copy the formula to all rows with text numbers. The new column now contains real numbers. Select all the results, copy them, then right-click on your original column and choose Paste Special. Click Values and then OK. This pastes the converted numbers back into the original column as values, not formulas. You can then delete the helper column.

Using the VALUE function for small batches

If you only have a handful of text numbers to convert, the VALUE function is straightforward. In an empty cell, type =VALUE(A1) where A1 is the cell with your text number. Press Enter. The result is a real number.

Copy this formula down for each text number you need to convert, the same way you would with the multiply-by-1 method. Then copy the results and paste them back as values into your original column. This method is most practical when you have fewer than 20 cells to convert, because setting up a helper column for just a few cells feels like extra work.

Checking your work after conversion

After you convert, check that your numbers are now right-aligned in their cells instead of left-aligned. Click one of the converted cells and look at the formula bar — there should be no apostrophe at the start. If you see an apostrophe, the conversion didn't take, and you'll need to try again.

Test a formula to make sure the numbers work. In an empty cell, type =SUM(A1:A10) (using your actual cell range). If the sum calculates correctly, your conversion worked. If it returns zero or an error, the numbers are still text and you'll need to repeat the conversion step.

Common mistakes to avoid

The most common mistake is forgetting to select the right range before using Text to Columns. If you select only one cell, Text to Columns will only convert that one cell. Always select the entire column or the full range of text numbers you want to convert.

Another mistake is pasting the results back as formulas instead of values. If you use the helper column method and paste as formulas, your numbers will revert to text if you delete the helper column. Always use Paste Special > Values to paste the converted numbers back.

Don't assume Text to Columns will work on cells with mixed content — if a cell contains "123 Main Street", Text to Columns will leave it as text because it's not purely numeric. This feature only converts cells that contain only numbers (and sometimes spaces or punctuation around them).

Frequently Asked Questions

Why won't my numbers add up in a SUM formula?

If your numbers are stored as text, SUM ignores them and returns zero. Convert them to real numbers using Text to Columns, the multiply-by-1 method, or the VALUE function. After conversion, the same SUM formula will calculate correctly.

Can I convert text numbers in the middle of other data?

Yes. Text to Columns works on any selection, not just full columns. Select only the cells with text numbers, then use Data > Text to Columns. The rest of your data stays untouched.

What if Text to Columns doesn't work?

Try the multiply-by-1 method instead. In a helper column, type =A1*1, copy it down, then paste the results back as values. If neither works, the cells may contain hidden characters or spaces. Click a cell and look at the formula bar to see exactly what's stored there.

Do I have to use a helper column with the multiply-by-1 method?

Yes, because you can't paste a formula result back into the same cell it references — Excel will create a circular reference error. Always use a helper column, then paste the results back as values into the original column.

Will converting text to numbers change how the numbers look in my cells?

Usually no. The numbers will look the same, but they'll be right-aligned instead of left-aligned. If you had custom formatting applied to the text, that formatting may change slightly after conversion.