How to Convert Text to Number

When a number is formatted as text, the value looks numeric but acts as text. This means you cannot conduct any mathematical operation or use the data in a numeric formula.

Fortunately, Excel has made it pretty convenient for users to transform data types while entering or manipulating data.

Excel will display a warning icon when you select a cell where a number has been formatted as text. Even when you don’t select the cell, you will see a green indent on the top-left corner of the cell. This is to help users identify that their numeric value is formatted as text.

After you’ve identified the cells, you can begin converting the texts to number.

Error Wizard

The quickest way to convert a number to text in Excel is by using the error wizard.

Select the cell with your numeric value and click on the warning icon. From the fly-out menu, choose Convert to Number.

Although this method faster, it will not work the best for you if you want to convert a bigger data set. Head to the other methods we’ve discussed below to convert larger values in Excel.

Via Text to Column

You can use Text to Column to convert multiple cells from text to number format in Excel at the same time.

If you’re dealing with a larger data set, you can use Text to Column to get the job done quicker.

Although there isn’t a specific Number format you can convert your text to, you can format your value under General. General Excel is the default cell type. Your value will still be manipulated as a number if the format is under General.

1. Select your data in the grid.
2. Head to Data > Text to Column in the menubar.
   
3. Click Next twice in the Text to Column wizard.
4. In the third window, make sure the selected format is General. Similarly, change the Destination cell to a different location.
   
5. Click OK.

From what we’ve gathered, the Text to Column wizard will not convert your text value to a number if you paste it into its existing location.

To test this, select your cell and hit F2 to edit your cell. When you click elsewhere, you will notice that the Error wizard appears again, alerting you that the cell is still formatted as text.

Format Cells as Number

You can use the Format Cells window to convert your text to a number. You can use this method to convert one or multiple values at the same time.

Excel may, however, still display the error wizard even after you format your cell as a number.

You can select the cell and hit enter to stop Excel from flashing the warning icon. Even if you don’t, don’t worry, Excel will still read your data as numbers.

1. Select your value from the worksheet.
2. Use Ctrl + 1 to open the Format cell window.
3. From the sidebar, head to Number.
   
4. Select OK.
   

Use Functions

You can use two functions, VALUE and NUMBERVALUE to convert a text to a number in Excel.

These functions work similarly, except NUMBERVALUE is locale independent.

Similarly, the NUMBERVALUE function will remove all spaces between each character. Depending on your needs, you can use either the VALUE or NUMBERVALUE function.

VALUE Function

On an empty cell, enter the VALUE function in the following format:

=VALUE(cell)

In this example, we’ve converted 5 numbers in the text format using the VALUE function. Firstly, we passed cell A2 as an argument and then used Flash Fill to fill in the remaining values.

NUMBERVALUE

Enter the NUMBERVALUE function in the following format:

=NUMBERVALUE(cell)

Here, we’ve entered the formula in cell F2. We passed B2 as an argument and used Flash Fill to convert the remaining values.

Use Paste Special to Paste Data as a Number

If you’re copy-pasting the values from a different spreadsheet, you can use Paste Special to convert your text to numbers.

Paste Special is a great tool to modify data even before pasting them to your grid. You can use this configuration to transform your value.

1. Open your workbook.
2. On the cell you wish to paste your value, enter 1. You must enter 1 for each value while converting multiple data.
3. Copy your value, and right-click on the first cell with 1.
   
4. Choose Paste Special.
   
5. In the Paste Special window, select the options for All under Paste, and Multiply under Operations.
   
6. Click OK.

Through Power Query to Transform Value

Although using the Power Query may seem intimidating, it’s personally our favorite method to convert large text datasets to numbers in Excel.

If all numeric data in your spreadsheet was transformed into text, your best bet is to load your data to the Power Query.

Power Query will automatically convert your data to General when you load it to your sheet.

1. Select your data from the worksheet.
2. Head to the Data tab and select From Table in the Get & Transform section.
   
3. Click OK in the Create Table prompt.
   
4. Select Close and Load from the home tab.