Microsoft Excel logo on a green background

When you enter numeric data into your spreadsheet, do you pay attention to how it’s formatted? Many times, numbers are formatted as text which can wreak havoc with formulas. Here are various ways to convert text to numbers in Excel.

Depending on how your number is entered as text, one method below may work better for you. For instance, you might have many nonadjacent cells where converting the cell format would be tedious. Or you might not see the warning symbol in order to use that method.

Change the Cell Format

A quick and easy way to convert text to a number is by changing the cell format on the Home tab. Use the drop-down box at the top of the Number section. Choose “Number” from the list.

Number selected for cell format

Alternatively, right-click the cell(s) and pick “Format Cells.” Select the Number tab and pick “Number” on the left. Optionally adjust the Decimal Places and click “OK.”

Number selected for cell format

You can also use the General format instead of Number if you like. General is Excel’s default number format. The difference with General compared to other number formats is that it uses scientific notation for numbers over 12 digits and rounds decimal numbers if the cell isn’t wide enough to accommodate the entire string.

RELATED: How to Convert Text to Date Values in Microsoft Excel

Use the Warning Symbol

If you enter your data and see a warning symbol on the bottom left corner, this is a fast way to convert the text. When you hover your cursor over the warning, you’ll see an explanation as in the screenshot below.

Warning for number stored as text

Advertisement

Click the drop-down arrow next to the warning and choose “Convert to Number.”

Convert to Number in the warning drop-down box

This removes the warning and formats the text as a number.

Select Text to Columns

Maybe you have an entire column of numbers formatted as text. Using the Text to Columns feature is the way to go. Note, however, that you can also use this option for a cell range.

RELATED: How to Use Text to Columns Like an Excel Pro

Select the column, go to the Data tab, and click “Text to Columns” in the Data Tools section of the ribbon.

Text to Columns on the Data tab

When the wizard displays, simply click “Finish.” Alternatively, you can click “Next” through each step without making any changes and hit “Finish” at the end.

Finish button in the wizard

Advertisement

This changes the format to Excel’s default General format for numbers. You can leave the format as-is or select the cells and choose “Number” in the Number section on the Home tab as described earlier.

Use Paste Special

You might have many cells or multiple columns that you need to convert to numbers from text. In this case, you can use the Paste Special with the Multiply operator.

RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel

Go to a blank cell that’s not formatted as text but as Number or General instead. Type the number 1 and press Enter or Return.

Number one formatted as a number

Copy the cell by right-clicking and selecting “Copy” or clicking the Copy button in the Clipboard section of the Home tab.

Copy button on the Home tab

Select all of the cells that you need to convert to numbers from text.

Either right-click and choose Paste Special > Paste Special or go to the Home tab and choose “Paste Special” in the Paste drop-down box.

Paste Special in the shortcut menu

Advertisement

Leave Paste set to “All” at the top. Under Operation at the bottom pick “Multiply”. Click “OK.”

Multiply in the Paste Special operations

This multiplies all values by the number 1, giving you the same numbers as results, and changes the format to that of the copied cell, either General or Number. You can then remove the number 1 you used.

Converted text to numbers with Paste Special

Insert the VALUE Function

The VALUE function gives you one more way to convert text to numbers in Excel. With this option, you’ll use a column to the right to convert the text.

RELATED: How to Add and Remove Columns and Rows in Microsoft Excel

If you need to insert a new column, right-click the column to the right of your text formatted column and pick “Insert.”

In the top cell (or second cell if you have a column header) enter the following replacing the cell references with your own.

=VALUE(A2:A6)

VALUE function with a range

Alternatively, you can enter a single cell reference in the parentheses to convert one cell. Then, copy the formula down to the remaining cells using the fill handle.

=VALUE(A2)

VALUE function and dragging the formula

Advertisement

You can then use the numbers in the new column as needed and do what you please with the original column.

Once you convert your text to numbers in Excel, your formulas are ready for their calculations with correctly formatted numbers!

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »