MS Excel: How To Convert Text To A Number In Excel

1. Convert Text To A Number Using Excel Error Checking

Excel has a special feature of Error Checking that is used to notify the Excel user about the presence of a cell in the Excel worksheet that contains a text representation of numbers. The error will be shown in a green triangle at the top left of the cell, this triangle is known as Error Indicator.

If you also facing the same error indicator issue while working with Excel then you can use the Error Checking option to convert the text representation to a number representation or convert the text representation of a number into an actual numeric value representation.

• Select a cell in the Excel worksheet, that you want to convert the representation from text to numbers (Cell should contain the error indicator and also will have a warning symbol at the side of the cell that contains a warning message that will display only when you hover on that warning symbol).
• From the Error message, Select Convert To Number option (the first option) to convert the text representation of the cell into numeric value representation or number representation.

2. Convert Text To A Number Using Excel Text To Columns

Excel Text To Columns option is also used to convert text to numbers and other data types conversions but the only drawback of this command is, Text to Column only works for one column at a time.

Follow the steps below to convert the text to numbers representation:

• Select the number of cells that you want to convert text to numbers but make sure you are selecting only one column at a time.
• Go to Text to Column option in the Data tab at the top beside Formulas tab.
• Click on Text to Column option and a pop up window will open. Inside the pop-up window of Text to Column:
  • Make sure Delimited option is checked (selected) and now click on the next button at the bottom of the pop-up window.
  • Uncheck all the Delimiters and again click on the next button at the bottom.
  • Here, you will ask to select the Column data format. So, select General and click on the Finish button.

3. Convert Text To A Number Using Excel Paste Special

Excel Paste Special option is also used by Excel users to convert Text to Numbers of a particular range in Excel worksheet cells.

Paste Special option will add a 0 value to each selected cell and by this procedure convert the text representations of numbers into numeric values but the only drawback of this method is, suppose a cell in the selected range contains non-numeric text than this method will ignore that text (unchanged).

• Enter 0 in any cell of the Excel worksheet.
• Select the cell containing value 0 and copy this cell by pressing ctrl+c on the keyboard.
• Now, select another cell that contains text representation and you want to convert it into number representation.
• Now, on the Home tab you will have an option of Paste, click on the Paste drop-down list to select the Paste Special option. (Paste Special shortcut: ctrl+alt+v)
• A Paste Special dialog box will open where you have to select Add and click on the OK button.

4. Convert Text To A Number Using The Excel Value Function

Excel Value function is used to convert a text into number representation. This is helpful when you want to extract a number value from a complicated text.

As shown in the below image, column A contains the text representation of numbers and column B used a Value function to convert text to numbers. Column D contains the output of column B