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.

**Table of Contents**hide

## 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
**W****arning 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.

**Read More: **How to Fix All Number Stored as Text in Excel

### 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 s**ection 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.

**Read More: **How Excel Formulas Convert Text to Number

### 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**C****5**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**M****ultiplication (*)**sign and write**1**along with it. - For example, we wanted to convert the text inside
**Cell C5**, so we**M****ultiplied (*)**the cell reference number**C****5**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**

## 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.