There are 4 ways you can convert text to number in Excel. This guide demonstrates the 4 ways you can convert texts to number formats.
If you format cells with numbers as Text, it can cause unexpected results. One of the problems you will experience with numbers formatted as text is that you will not be able to sum the numbers. The cell will also display an error message.
To resolve the error, you have to convert the text to number.
How to Convert Text to Number in Excel
In Excel, you can convert text to number format in 4 ways:
- From the Error Message
- Using Text to Columns
- Formula Method
- Paste Special and Multiply
Method 1: Convert Text to Number in Excel From the error Message
- Select all the cells with the error message.
- Then click on the error message and select Convert to Number.
To convert the SUM cell:
- Right-click the cell and click Format Cell.
- When the Format Cells option opens, select Number. Then click Ok.
- Next, double-click the SUM cell and press Enter key on your keyboard.
Method 2: Convert Text to Number in Excel Using Text to Columns
To use this method:
- Click on top of the column to select the entire column.
- Next, click the Data tab. Then click Data Tools. Finally, click Text to Columns. The Convert Text to column wizard will open.
- Click Finish. The format have been converted. See the second image below.
You may have to SUM the numbers again and convert the SUM cell as I demonstrated earlier in this guide.
Method 3: Convert Text to Number in Excel Using Formula Method
You can also use the VALUE() function to convert text to number in Excel.
Here is how:
- On a new cell, enter the following formula =VALUE(). Within the (), enter the cell you wish to convert. Then press Enter key.
- To replicate the =VALUE() formula to the other cells, click on C2, then move your mouse towards the bottom right edge of the clicked cell. When the + sign turns black, drag the formula to all the cells you wish to convert.
Method 4: Convert Text to Number in Excel Using Paste Special and Multiply
- Enter the number, 1 into a blank cell that does not have the number problem. Select the blank cell with the number, 1. Then Press Ctrl + C on your keyboard to copy the cell. Next, select the cells that have the numbers stored as text.
- Then right-click the selected cells and click Paste Special.
- On the Paste Special options page, select Multiply and click Ok.
I hope you were able to resolve your Excel error using one of the methods described above. If you have question or comment use the “Leave a Reply” form at the end of the page.
Alternatively you can share how you converted text to numbers and fixed this problem. This will benefit other readers.