|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).
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 pop 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.
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.