How to Convert Text to Number in MS Excel 2016?

In this section, we will see how you can convert text to number in Excel. Excel allows you to choose the format for your cells such as you can keep the format of your cell as Date, Time, % Percentage, Fraction, Text, Number etc.

If your cell is in Text format it generates an unexpected result for mathematical formulas. Like, look at the picture below, in a cell, I have written a formula =12+4 and pressed Enter.

Just because the cell is in Text format the content remains the same even after I press Enter. If it would be in Number format it would have generated the result 16 in the cell after Enter is pressed.

We will study five methods to convert text to number in Excel

1. Change Format of Cells
2. Using Error Message
3. Adding 0 or Multiplying 1
4. Using Formula
5. Text to Column

Steps to Convert Text to Number in MS Excel 2016:

Method 1: Change Format of Cells

Using this method, you can change the format of the entire column. Remember that the cell you select for this method is in the same column otherwise, it won’t work.

Step 1: Select the cells in Text format or you can even select a range of cells and go to the drop-down box in Number group of Home tab.

Step 2: Click the drop-down box and select Number from the options and you are done.

All the cells will now be in Number format.

Method 2: Using the error message

This method is fine if you have fewer cells to correct. As using this method you are able to correct one cell at a time.

Step 1: If your cell is in Text format, and you click on the cell it will show error sign in yellow color. Click on the error sign, it will display the convert option.

Step 2: Among the options click on Convert to Number. After this, it will display the content of your cell into the Number format.

Method 3: Adding 0 or Multiplying 1

Using this method you can initially convert a single cell in Number format then you can drag-down the cell to apply the procedure to rest of the cells.

Step 1: Suppose you have an entire column in the text format and you want to convert it into number format. Select a new cell with the general format corresponding to the first cell in text format and type “=”. Then choose the first cell of text format.

Automatically the cell number will start displaying beside the = sign. As you can see in the picture below. Now either type “+ 0” add zero or “ x 1” multiply 1.

Click Enter and you will see that the content of the cell in Text format is in the Number format in the newly selected cell.

Step 2: To convert all cells in Text format to Number format. Select the newly converted cell in step 1 and drag it down to apply the formula of adding 0 or multiply 1 to all the corresponding cells. As you can see in the picture below at the left hand I have cells in Text format and at the right hand I have converted cells in number format using method 3.

Method 4: Using Formula Convert Text to Number

Using the Value(text) formula you can convert the text to number without changing the content of a cell.

Step 1: Select a new cell which is not in Text format and type the formula =value(text) and inside the parenthesis select the cell of text format which you want to convert to number. Click Enter. Done, here also you can drag down the cell to apply formula to rest of the cells.

Method 5: Text to Column

Though it is used to split the text in the cell to multiple columns. It can be used to convert Text to Number. It is expected that the Text format cell has space or apostrophe at the beginning. And Text to Column option deletes all the delimiters of the selected cell. So the cell automatically gets converted to Number format.

Step 1: Select a cell or you can even select a range of the cells. Go to the Data tab and in the Data Tools group click Text to Columns.

A convert Text to Column Wizard will be opened. As you are only converting Text to Number you need not specify any parameters and you can directly click on Finish. All the selected cells are now in Number Format.

These are the methods you can choose to convert text to number. Hope the content is useful. Keep reading.