4 Ways To Convert Text To Number In Excel

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:

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.

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.

Conclusion

I hope you were able to resolve your Excel error using one of the methods described above. I also hope you found the Itechguide helpful. If it was helpful, kindly share how you converted text to numbers and fixed this problem on our forum at Itechguides Community Forum. If you have questions or comments kindly post them on Itechguides Community Forum. Our community staff and members are willing to provide you with a prompt response.