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

In this article, we will show you how to convert bulk text to numbers in Excel in 6 ways. Consider the following dataset of Product and Order IDs.

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

When the numbers of your worksheet are stored as text, Excel will provide a notification on the top left corner of the cell, stating: The number in this cell is formatted as text or preceded by an apostrophe.

Method 1 – Use the Convert to Number Feature for Changing Bulk Text to Number

Select all the cells containing numbers as text.
Click on the Warning Icon.
Choose Convert to Number.

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.

Method 2 – Change the Cell Format to Convert Bulk Text to Number in Excel

Select the Cell range C5:C14 containing numbers as text.
Click on the drop-down button from the Number Format drop-down list and select Number.

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.

Method 3 – Apply Paste Special to Alter Text to Number in Excel en Masse

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

Select Add from the Operation section in the Paste Special box.
Click OK.

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.

Method 4 – Change a String to a Number with the Text to Columns Feature in Excel

Select all the cells containing numbers as text.
Select Data then Data Tools, and choose Text to Columns.

Select Delimited from the Original data type section in the Convert Text to Columns Wizard pop-up box.
Click Finish.

You will get the converted numbers stored as text in Excel.

Method 5 – Implementing Formula to Convert Text to Number in Excel

Click on the cell that you want to get the result (Cell D5 in our case).
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, the function becomes:

=VALUE(C5)

Press Enter.

The text value of Cell C5 is converted as a number value in Cell D5.
Drag the row down by the Fill Handle to apply the formula to the rest of the cells.

Method 6 – Turn Text into Numbers in Excel with Mathematical Operations

The following operations implicitly convert textual values into numbers if possible:

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

Steps:

Click on the cell that you want to get the result (Cell D5 in our case).
Write the cell reference number that you want to convert, put a Multiplication (*) sign, and write 1 along with it.
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

Press Enter.

Note: You can also use C5+0 or C5/1.

The text value of Cell C5 is converted as a number value in Cell D5.
Drag the row down by the Fill Handle to apply the formula to the rest of the cells.

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 the Practice Workbook

Convert Text to Number Bulk.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!