How to Convert Text to Number in Excel

  • Post author:
  • Post category:Uncategorized

To convert text to number we can use error message that appears in the upper left corner of the cell, simple Copy-Paste special option or VALUE function.

This step by step tutorial will assist all levels of Excel users in learning how to convert text to number.

Convert Text to Number Using Error Message

We can easily recognize number formatted as text with a green triangle in the upper left corner of the cell. When we put the cursor on the triangle the error message will appear.

Figure 1. number stored as text

There are also other signs that number is formatted as text:

  • When we select the numbers stored as text, the Status bar will display count of the cells. However, for numbers it will appear as sum and average of the cells
  • The Number stored as the text has left alignment in the cell while the number has the right
  • Numbers stored as text have the leading apostrophe in the formula bar

Figure 2. convert number to text

To convert string to number, follow the steps:

  • Select the numbers formatted as text
  • Click on the error message and choose Convert to Number
  • Excel converts text to numbers in all selected cells

Figure 3. Convert text to number

Change Text to Number with Copy and Paste Special

Another way to convert text to numbers is to use Copy, Paste Special Excel option:

  • Select the numbers stored as text with errors, right-mouse click and choose Copy

Figure 4. Copy numbers stored as text

  • Right-mouse click again and choose Paste Special
  • In Paste Special choose Values and Add and press OK

Figure 5. Change text to number

  • As a result, Excel converts text to numbers

Figure 6. change the text to number with Copy, Paste Special

Convert String to Number with VALUE Function

Another way to change the text to a number is to use VALUE function:

  • Select the cell C3 and insert the formula =VALUE(B3)
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 7. How to convert text to number with VALUE function

The VALUE function has one argument, the number stored as text. When we copy the formula down to the other cells, Excel converts all text cells to number.