If your spreadsheet contains text representations of numbers, rather than actual numeric values, this may produce errors when you try to use these values in calculations. Therefore, this page describes four different methods that can be used to convert text to numbers.
|Excel Convert Text to Number Index:|
|Text to Number Using Error Checking|
|Text to Number Using Text to Columns|
|Text to Number Using Paste Special|
|Text to Number Using Excel Functions|
Excel has an error checking option that can alert you to the presence of cells containing text representations of numbers. This will be seen as a small colored triangle (the error indicator) in the top left corner of your cells (see below).
If your cells display this error indicator, you can use Excel error checking to convert text representations of numbers into actual numeric values.
To do this:
Select the cell (or cells) containing the values that you want to convert.
This will cause a warning symbol to pop up at the side of the cell(s). If you then hover over the warning symbol, a warning message is displayed (see below).
Click on the warning symbol, to bring up the Error Checking menu (see rightabove).
Select the Convert to Number option to convert the cell values into numbers.
In order for the above method to work, you need to ensure that you have the Excel Error Checking option for numbers stored as text enabled.
To access this option:
The Excel Text to Columns command will also convert between Excel data types. However, this command will only work on one column at a time.
To use Excel Text to Columns to convert text to numbers:
From the Data tab on the Excel ribbon, select the Text to Columns option (see rightabove).
This will cause the Convert Text to Columns wizard to pop up. Within this:
You should now be offered a selection of Column Data Formats. Select General and click the Finish button.
You can also use the Excel Paste Special command to convert text to numbers in a range of Excel cells.
The method described below adds the value zero to each cell in the selected range. This procedure converts text representations of numbers into numeric values, but leaves any cells containing non-numeric text unchanged.
To do this:
From the Home tab of the Excel ribbon, select the option Paste → Paste Special (see rightabove).
Note that the keyboard shortcut for this is Ctrl + Alt + V.
This will cause the Paste Special Dialog box to open up, as shown below.
The Excel Value function converts a text value into a number. This might be useful if you want to extract the numeric values from a more complicated text string.
Column A of the spreadsheets below contain text representations of numbers. The Excel Value function is used in column B of the spreadsheet, to convert the text values into numbers.
In the spreadsheet below, the Excel Value function is used in combination with the Excel Left function, to extract the numeric value from the text string in cell A1 of the spreadsheet.
Note that, if the text argument that is supplied to the Value function cannot be interpreted as a numeric value, the function returns the #VALUE! error.
Methods of Converting Excel Text to Numbers are discussed further on the Microsoft Office Support website.