Convert Text to a Number in Excel

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.

Convert Text to a Number Using Excel Error Checking

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.

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 right above ).

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:

1. Click on the File tab (or the Microsoft Office Button in Excel 2007), and select Options.
2. Within the Excel Options window, select Formulas.
3. Within the Error Checking section, make sure the option Enable background error checking is checked.
4. Within the Error checking rules section, make sure the option Numbers formatted as text or preceded by an apostrophe is checked.
5. Click OK.

Convert Text to a Number Using Excel Text to Columns

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:

1. Select the range of cell(s) that you want to convert (these must not span more than one column).

From the Data tab on the Excel ribbon, select the Text to Columns option (see right above ).

This will cause the Convert Text to Columns wizard to pop up. Within this:

• Make sure the Delimited option is selected and click Next >;
• Make sure that none of the Delimiters are selected and then click Next > again;

You should now be offered a selection of Column Data Formats. Select General and click the Finish button.

Convert Text to a Number Using Excel Paste Special

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.

1. Type a zero into any spare cell in your spreadsheet.
2. Select the cell containing the zero and copy this cell (the easiest way is to use the keyboard shortcut, Ctrl + C ).
3. Select the cells containing the text that you want to convert to numbers.

From the Home tab of the Excel ribbon, select the option Paste → Paste Special (see right above ).

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.

Convert Text to a Number Using the Excel Value Function

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.

Value Function Examples

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.