How to Convert Text to Number in MS Excel

Sometimes system-generated documents contain only text fields. Even the fields that look like numbers, are formatted as text. That means you cannot do any calculations on those fields. You first have to convert these text fields to numbers in MS Excel.

This tutorial will show you different approaches for converting text into numbers. 

3 Methods to convert text to numbers in Microsoft Excel:

  • Use the warning sign next to the cell
  • Use the VALUE formula
  • Use the paste special feature

Let us begin by looking into the methods one by one.


Method #1: Use the Warning Sign Next to the Cell

If you are new to MS Excel, this would be an easy method for you to use. It is a fairly simple and convenient method.

Step #1: Open your Excel

Open the Excel file where you want to convert text into numbers. Make sure the workbook has numbers formatted as text to perform this exercise.

Step #2: Click on the warning sign

If you click on the cell formatted as text, a warning sign () appears on the side of that cell. 

(Excel considers storing numbers as text as an error, hence the use of the word “error”.)

Click on the warning sign and you’ll get different options to deal with the error. 

Step #3: Select ‘Convert to Number’ option 

Under the warning sign, click on the Convert to Number option to convert the text into numbers.

The text in the cell will get converted into numbers and the warning sign will disappear.

To check that it is correct, in the format box on the top right of the Home ribbon, the format will display as General.

General is the default MS Excel format for numbers.


Method #2: Use the Value Formula

The Value formula is the simplest method for the conversion of text to numbers.

Step #1: Open your Excel

Open the Excel file where you want to convert text into numbers. Make sure the workbook has numbers formatted as text to perform this exercise.

Step #2: Use the Value formula

Double click on the cell next to the text field, where you want the converted number to be displayed.

Enter the formula 

=VALUE(text)

  • text would be the cell containing the number in text format, in our example, that is A1.

Press Enter on your keyboard.

The text gets converted into numbers. 

To check that it is correct, in the format box on the top right of the Home ribbon, the format will display as General.

General is the default MS Excel format for numbers.


Method #3: Use the Paste Special Feature

The paste special feature is useful for converting a large set of text to numbers in a single go.

Step #1: Open your Excel

Open the Excel file where you want to convert text into numbers. Make sure the workbook has numbers formatted as text to perform this exercise.

Step #2: Use the multiply feature

First, type “1” in any cell in the worksheet and copy it. 

For copying, you can press Ctrl + C or click on Copy after right-clicking on the cell.

Step #3: Apply ‘paste special’ 

After copying the digit, click on the cell containing the text. 

Press Ctrl + Alt + V in Windows and ^ + ⌘ + V on Mac. 

Or

Click right and select Paste Special from the menu that opens, and again Paste Special at the bottom of the window.

In the Paste Special window, select multiply and click the OK button or press Enter.

The text will get converted into numbers. 

To check that it is correct, in the format box on the top right of the Home ribbon, the format will display as General.

General is the default MS Excel format for numbers.


Conclusion

When you’ve applied formulas to numbers and they don’t seem to work, always check their formatting. If the numbers are stored as text, use the methods given above to convert text into numbers. This should make the formulas work in the intended manner.

Leave a Comment