How to Convert Bulk Text to Number in Excel (6 Suitable Ways)

Sometimes the numbers in your worksheet don’t act like numbers; they don’t execute any kind of arithmetic operations as they should, and they even may produce errors. The reason for this is, that even though they look like numbers, they are actually formatted as text. In this article, we will show you how to convert bulk text to number in Excel in 6 suitable ways.

How to Convert Bulk Text to Number in Excel: 6 Ways

How will you understand that the numbers in your worksheet are actually stored as text? Well, Excel has a built-in error-checking function to notify you when there is an error in any cell. This looks like a small rotated yellow square icon with an Exclamatory (!) mark inside it. When you put the pointer of your mouse over it, it shows you the problem that your cell has.

So, when the numbers of your worksheet are stored as text, it will give you a notification sign on the top left corner of the cell, stating: The number in this cell is formatted as text or preceded by an apostrophe.

Below in this section, we will show you how to convert bulk text to number in Excel in 6 suitable ways.

1. Use Convert to Number Feature for Changing Bulk Text to Number

If your cell is displaying the warning sign (yellow square icon) then follow the steps below.

First, select all the cells containing numbers as text.
Then, click on the Warning Icon > Convert to Number.

As a result, this will convert all the numbers stored as text to numbers in Excel.

Note: Even though this method is really quick and easy, it is not recommended when working with a large range of cells. Not only does this process take a very long time, but it may even cause Excel to crash. So, to avoid any kind of risks, continue reading this article to explore some more effective options to convert text to numbers in Excel.

2. Change Cell Format to Convert Bulk Text to Number in Excel

When your cell holds any kind of value, then there is a feature in Excel that shows you the type of the value that your cell has, on the Home tab in the Number group. You can also utilize this feature to change the format of your data.

First, select the Cell range C5:C14 containing numbers as text.
Second, click on the drop-down button from the Number Format drop-down list and select Number.

Therefore, this will convert all the numbers stored as text to numbers.

Note: In some cases, this method won’t work. For instance, if you apply the Text format to cells, enter numbers in those, and then change the cell format to Number, then the cell will remain formatted as text.

3. Apply Paste Special to Alter Mass Text to Number in Excel

Compared to the last two techniques, this method takes a few more steps to execute the task but it works more accurately than the previous methods. Steps for utilizing Paste Special feature to convert text to number in Excel are given below.

In the beginning, copy an empty cell from your worksheet by pressing Ctrl + C.
Otherwise, right-click on the cell and select Copy from the list.
Then, select all the cells containing numbers as text > right-click and select Paste Special option from the list.
You can also press Ctrl + Alt + V on your keyboard to Paste Special.

Afterward, select Add from the Operation section in the Paste Special box.
Following this, click OK.

As a result, this will convert all the numbers stored as text to numbers in Excel.

Explanation: We are applying two tricks in this method.

Trick 1: Running a mathematical operation (Add) to convert the text value to a number value.
Trick 2: Copying a null value and adding it with the original value, because adding a null value with anything doesn’t change the actual value.

4. Change String to Number with Text to Columns Feature in Excel

Excel’s Text to Columns feature is a multi-purpose feature in executing various Excel-related tasks. And to change texts to numbers is just a two-step process.

First, select all the cells containing numbers as text.
Second, select Data > Data Tools > Text to Columns.

Third, select Delimited from the Original data type section in the Convert Text to Columns Wizard pop-up box.
Lastly, click Finish.

That’s it. You will get the converted numbers stored as text in Excel.

5. Implementing Formula to Convert Text to Number in Excel

Implementing a formula is the most effective and secure way to accomplish any task in Excel. Microsoft Excel has a function called the VALUE function to convert a string to a number. Here we will implement the VALUE function to alter our text to number.

In the beginning, click on the cell that you want to get the result (Cell D5 in our case).
In that cell, write the VALUE function and pass the cell reference number of the cell that you want to convert inside the bracket.
For example, we want to convert the text inside Cell C5, so we passed the cell reference number C5 inside the VALUE function.
So it became like this,

=VALUE(C5)

Press Enter.

Notice that, the text value of Cell C5 is converted as a number value in Cell D5.
Now, drag the row down by Fill Handle to apply the formula to the rest of the cells.

6. Turn Text into Number in Excel with Mathematical Operations

Another fun and easy way to convert text to numbers are by performing simple mathematical operations that don’t change the original value.

Operations such as,

Adding Zero (0) with the original value
Multiplying the original value with 1.
Dividing the original value by 1.

We applied multiplication in our example. You can implement any arithmetic operations from the 3 listed above that you like. Steps to implement multiplication to turn text into numbers are shown below.

In the beginning, click on the cell that you want to get the result (Cell D5 in our case).
In that cell, write the cell reference number that you want to convert, put a Multiplication (*) sign and write 1 along with it.
For example, we wanted to convert the text inside Cell C5, so we Multiplied (*) the cell reference number C5 with 1.
So it looked like this,

=C5*1

Lastly, press Enter.

Note: If you want to perform the arithmetic operation, Addition, then simply write it as, C5+0. On the other hand, if you want to perform the arithmetic operation, Division, then simply write it as, C5/1.

Notice that, the text value of Cell C5 is converted as a number value in Cell D5.
Now, drag the row down by Fill Handle to apply the formula to the rest of the cells.

Explanation: The magic of this method is,

Running mathematical operations (Addition or Multiplication or Division) leads to convert the text value into a number value.
Multiplying or dividing any value with 1 or adding any value with 0 doesn’t change the original value.

Things to Remember

The VALUE function is preferable to use when the text string represents a number.
If there is any space inside the text string, then nest the TRIM function with the VALUE function. Therefore, the formula will look like this.

=TRIM(VALUE(C5))

You can use the Text to Columns feature for converting dates, numbers and time formats.

Download Practice Workbook

Convert Text to Number Bulk.xlsx

Conclusion

This article showed you how to convert bulk text to numbers in Excel in 6 suitable ways. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

<< Go Back to Convert Text to Number in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!