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

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


Download Practice Template

You can download the free practice Excel template from here.


6 Easy Ways to Bulk Convert Text to Number in Excel

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 bulk convert text to number in Excel in 6 ways.


1. Using Convert to Number Feature in Excel

If your cell is displaying the warning sign (yellow square icon) then,

  • Select all the cells containing numbers as text.
  • Click on the warning icon -> Convert to Number.

convert to number feature to convert text to number bulk in excel

This will convert all the numbers stored as text to numbers in Excel.

result of convert to number feature to convert text to number bulk 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 this process takes a very long time, but also 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 number in Excel.

Read More: How to Convert Text to Number with Excel VBA (3 Examples with Macros)


2. By Changing the Format to Convert Bulk Text to Number in Excel

When your cell holds any kind of value, then there is a feature in Excel which 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.

Steps:

  • Select all the cells containing numbers as text.
  • Click on the drop-down button from the Number Format drop-down list and select Number.

changing cell format to convert text to number bulk in excel

This will convert all the numbers stored as text to numbers in Excel.

result of changing cell format to convert text to number bulk in excel

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.

Read More: How to Fix All Number Stored as Text in Excel (6 Easy Solutions)


Similar Readings


3. Utilizing Paste Special to Mass Alter 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 of utilizing Paste Special feature to convert text to number in Excel:

  • Copy an empty cell from your worksheet.

Click on an empty cell and press Ctrl + C to copy. You can also right-click on the cell and select Copy from the list.

  • Then select all the cells containing numbers as text, right-click the mouse and select Paste Special option from the list. You can also press Ctrl + Alt + V on your keyboard to Paste Special.

paste special method to convert text to number bulk in excel

  • From the pop-up Paste Special box, select Add from the Operation
  • 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 as a number value.
    • Trick 2: Copying a null value and adding it with the original value, because adding null value with anything doesn’t change the actual value.

Read More: How to Convert Text with Spaces to Number in Excel (4 Ways)


4. Using Text to Columns Feature to Change String to Number 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.

Steps:

  • Select all the cells containing numbers as text.
  • Go to Data -> Text to Columns from Data Tools

text to columns feature to convert text to number bulk in excel

  • In step 1 of the Convert Text to Columns Wizard pop-up box, select Delimited from the Original data type.
  • Click Finish.

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

Read More: How to Convert String to Double in Excel VBA (5 Methods)


Similar Readings


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.

Steps:

  • Click on the cell that you want to get the result (Cell C5 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 B5, so we passed the cell reference number B5 inside the VALUE function.
    So it became like this,
    =VALUE(B5)
  • Press Enter.

formula to convert text to number bulk in excel

Notice that the text value of Cell B5 is converted as a number value in Cell C5.

  • Now drag the row down by Fill Handle to apply the formula to the rest of the cells.

result of formula to convert text to number bulk in excel

This will convert all the numbers stored as text to numbers in Excel.

Read More: How to Convert String to Long Using VBA in Excel (3 Ways)


6. Utilizing Mathematical Operations to Turn Text into Number in Excel

Another fun and easy way to convert text to numbers is 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.

Steps:

  • Click on the cell that you want to get the result (Cell C5 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 B5, so we multiplied (*) the cell reference number B5 with 1.
    So it looked like this,
    =B5*1
    • If you want to perform the arithmetic operation, Addition, then simply write it as, B5+0
    • If you want to perform the arithmetic operation, Division, then simply write it as, B5/1
  • Press Enter.

mathematical operation to convert text to number bulk in excel

Notice that the text value of Cell B5 is converted as a number value in Cell C5.

  • Now drag the row down by Fill Handle to apply the formula to the rest of the cells.

result of mathematical operation to convert text to number bulk in excel

Explanation: The magic of this method is,

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

Read More: How to Convert Alphabet to Number in Excel (4 Easy Ways)


Conclusion

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


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo