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

**Table of Contents**hide

## Download Practice Workbook

You can download the free practice Excel template from here.

## 6 Suitable Ways to Convert Bulk 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 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 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. 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 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.

- 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 (6 Easy Solutions)**

**Similar Readings**

**Convert Date to Week Number of Month in Excel (5 Ways)****How to Convert Month to Number in Excel (3 Easy Methods)****Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)****Convert Degrees Decimal Minutes to Decimal Degrees in Excel****How to Convert Degrees Minutes Seconds to Decimal Degrees 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 to Convert Text with Spaces to Number in Excel (4 Ways)**

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

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

**Similar Readings**

**Convert Hours and Minutes to Decimal in Excel (2 Cases)****How to Fix Convert to Number Error in Excel (6 Methods)****Convert Scientific Notation to Number in Excel (7 Methods)****How to Convert Percentage to Number in Excel (5 Easy Ways)****Convert Date to Number in Excel (4 Methods)**

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

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

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

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

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

## 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. Follow **ExcelDemy** for more tutorials.

## Related Articles

**How to Convert Month Name to Number in Excel (5 Suitable Ways)****Subtract Time and Convert to Number in Excel****How to Convert Decimal to Whole Number in Excel (10 Easy Ways)****Convert Green Triangle to Number in Excel (6 Easy Ways)****How to Convert Currency to a Number in Excel (6 Easy Ways)****Excel Convert to Number Entire Column (9 Simple Methods)****How to Convert Percentage to Whole Number in Excel (4 Methods)**