If you are looking for simple ways to know how Excel formulas convert Text to Number, this article will serve the purpose.

While working with data in Excel, we often encounter some data that are numbers but are mistakenly **stored as texts**. To get rid of this silly mistake, in Excel we can easily convert text to number using Excel formulas by following some simple methods.

So, let’s start with this article and learn all the steps on how Excel formulas convert text to number.

## Download Practice Workbook

You can download the practice workbook from the link below.

## How Are Numbers Indicated as Text in Excel

Do you want to know how to tell if a number is entered as text in Excel?

In Excel, **numbers **are by default entered as **right **aligned and **text **is **left **aligned. If you notice that your data is left aligned, then the number must be in **Text **format.

Another way to check is to see if there is any green flag at the top left corner of your cell. If you select the cell an error checker will appear with a message as **Number Stored as Text**.

## Why Is Converting Text to Number Impactful in Excel?

There are a number of ways a number can be stored as text. For example, having a leading apostrophe or storing the numbers while text format is applied to the cell, etc. can be the cause of storing numbers in text format. When a number is stored in text format, we can’t do any mathematical operation or apply any formula with this data. Which is a major drawback. So, it is crucial to converting text to numbers in Excel.

## How Excel Formulas Convert Text to Number: 3 Easy Ways

In this article, we are going to learn **3 **easy methods of how Excel formulas convert text to number.

We have used Microsoft **Excel 365 **version for this article, you can use any other version according to your convenience.

In the following dataset, we have **5 **numbers that are mistakenly stored in text format. We will convert them into number format.

### 1. Using VALUE Function to Convert Text to Numbers in Excel Formula

By using **the VALUE function** we can easily convert text to numbers. If a text string represents a number, then the **VALUE **function will convert the** text string into that number**. Let’s follow the steps outlined below.

- Insert the following formula in cell
**C5**.

`=VALUE(B5)`

- Press
**Enter**and use Excel’s**Fill Handle**tool to get the rest of the data like the following picture.

**B5**represents the cell of

**Number as Text**column.

#### How to Check If Value Is Number or Not

At this stage, we will check whether the texts are converted to numbers or not. To do this we will use **the ISNUMBER function**. If the content of a cell is a number then the **ISNUMBER **function will return **TRUE**, otherwise, it will return **FALSE**.

- Select cell
**D5**and Insert the following formula.

`=ISNUMBER(B5)`

- Press
**Enter**and use Excel’s**Fill Handle**tool to get the rest of the data like the following picture.

**Column B**are numbers or not using the

**ISNUMBER**function.

- After that, select cell
**E5**and Insert the following formula.

`=ISNUMBER(C5)`

- Press
**Enter**and use Excel’s**Fill Handle**tool to get the rest of the data like the following picture.

**Column C**are numbers or not using the

**ISNUMBER**function.

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

### 2. Convert Text to Number in Excel Using Arithmetic Operation in Formula

Multiplying by **1 **is one of the most popular methods to convert text to number. Let’s see the steps mentioned below.

- Select
**Cell C5**and insert the following formula.

`=B5*1`

- Press
**Enter**and then drag down the**Fill Handle**tool to copy the formula to the rest of the cells. - Thus, you can convert text values to numbers in Excel.

**Cell B5**represents the cell of

**Number as Text**column. We multiplied it by

**1**to convert the text to a number.

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

### 3. Convert Text with Space to Numbers Using VALUE, TRIM and CLEAN Functions in Excel

Suppose you have extra spaces and non-printing characters in your dataset. You want to convert text values to numbers and remove those extra spaces and non-printing characters. You can use the **VALUE**, **TRIM** and **CLEAN** functions to do that.

Here in **Cell C5**, we will see that the **CLEAN **function will remove extra spaces and non-printing characters of **Cell B5. **Then the **TRIM **function will remove any leading or trailing spaces and the **VALUE **function will convert text to number.

- Insert the following formula in cell
**C5**.

`=VALUE(TRIM(CLEAN(B5)))`

- Press
**Enter**and use Excel’s**Fill Handle**tool to get the rest of the data like the following picture.

**B5**represents the cell of

**Number as Text**column.

## How to Convert Text to Number Without Using Formulas in Excel

You can convert text to numbers in different ways without using any formulas in Excel. Here, you will find some of those methods below.

### 1. Use Text to Columns Option to Convert Text to Number in Excel

In this section of the article, we will learn to use the **Text to Column **feature of Excel to **convert text to number**. Let’s follow the steps mentioned below.

- Select the column of
**Number as Text**. - After that, go to the
**Data**tab >>**Text to Columns**option.

- A dialogue box will open on your screen like the following picture, and click
**Next**from the dialogue box.

- Check the box of
**Tab**as marked in the following image and click**Next**.

- Insert your destination cell. In this case, we have selected cell
**C5**and clicked on**OK**.

- You will be able to see the following image on your screen.

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

**Similar Readings**

**How to Convert Percentage to Whole Number in Excel (4 Methods)****Excel VBA to Convert Textbox Value to Number (2 Ideal Examples)****How to Convert Date to Week Number of Month in Excel (5 Ways)****Convert Degrees Decimal Minutes to Decimal Degrees in Excel****How to Convert Percentage to Decimal in Excel (7 Methods)**

### 2. Apply Error Checking Button to Change Text to Number

Using the **Error Checking Button **is the simplest way to **convert text to number**. Let’s learn the steps mentioned below to do this.

- Firstly, select the column of
**Number as Text**and press**Ctrl+C**.

- Click on cell
**C5**and press**CTRL+V**. You will get the following table on your worksheet.

- Now, select cell range
**C5:C9**. Click on the error icon as marked in the following picture.

- Select
**Convert to Number**from the drop-down.

- Your data will be converted to a number format like the following image.

**Read More:** **How to Fix Convert to Number Error in Excel (6 Methods)**

### 3. Convert Text into Number by Changing Number Format in Excel

You can change the **Number **format of a cell to convert text to number in Excel. Here are the steps to do that.

- Select
**Cell B5**. Go to the**Home**tab >> select**Number**from**Number**group. - Double-click on the cell to go to
**Enter**mood and then press**Enter**. - Thus, you can convert text to number changing Number format in Excel.

### 4. Change Text to Number with Paste Special Feature

You can also use the **Paste Special** feature to change the text to number in Excel. Here are the steps to do that.

- Select the column of
**Number as Text**and press**Ctrl + C**.

- Then, select
**Cell C5**and press**Ctrl + Alt + V**to open the**Paste Special**box.

- Now, select
**Values**from the**Paste**options and**Add**from the**Operation**options. After that, click on**OK**.

- Thus, it will copy only the values from your selected cell range.

### 5. Use Power Pivot Feature to Convert Text to Number in Excel

We can **convert text to numbers **by using **Power Pivot **also. Let’s learn the following steps to do this in Excel.

#### Step 01: Enabling Power Pivot Option

If you do not have the **Power Pivot **option enabled in your Excel workbook follow the steps given below to do that.

- Go to the
**File**tab.

- Select the
**Options**tab.

- An
**Excel Options**dialogue box will open like the following image. Go to the**Add-ins**tab. - Select
**COM Add-ins**from the**Manage**drop-down options. Then, click on**Go**.

At this stage, another dialogue box will open named **COM Add-ins **like the following image.

- Check the box of
**Microsoft Office PowerPivot for Excel 2013**and click**OK**.

#### Step 02: Using Power Pivot

Now, go through the steps given below to use **Power Pivot **to convert text to number in Excel.

- Go to the
**Power Pivot**tab. Now, select the dataset and click on**Manage**.

- After that, you will see the following Power Pivot box on your screen.
- Click on the cell below
**Add Column**and insert the following formula there.

`=VALUE(PastedTable[Number as Text])`

**Note: **To insert this formula, first, type in **=VALUE(**. Then double-click on the **Number as Text **cell. Then close parenthesis.

- After entering the formula, you will see the following output on your worksheet.
- Click on
**Save**as shown in the image given below and then click on**Minimize**.

#### Step 03: Inserting Pivot Table

Finally, to show the results in your Excel worksheet go through the steps given below.

- Go to the
**Insert**tab >>**Pivot Table**drop-down >>**From Data Model**option.

- After that, a dialogue box will open. Select
**New Worksheet**and click**OK**in the dialogue box like the following image.

Another dialogue box named **Pivot Table Fields **will open on your worksheet.

- Click on
**PastedTable**. Check the boxes of**Number as Text**and**Calculated Column 1**.

- Thus, you will have the following table on your worksheet.

**Read More:** **How to Bulk Convert Text to Number in Excel (6 Ways)**

## Practice Section

For doing practice, we have provided a **practice section **in each worksheet on the right side. Please do it by yourself.

## Conclusion

Finally, we have to the very end of the article. I truly hope that this article was able to guide you to learn the methods of how excel formulas convert text to number. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, **ExcelDemy**. Happy learning!

## Related Articles

**How to Convert Exponential Value to Exact Number in Excel (7 Methods)****Excel Convert to Number Entire Column (9 Simple Methods)****How to Convert Degrees Minutes Seconds to Decimal Degrees in Excel****Convert 3 Letter Month to Number in Excel (8 Suitable Methods)****How to Convert Hours and Minutes to Decimal in Excel (2 Cases)****Convert Time to Number in Excel (5 Easy Methods)****How to Convert String to Double in Excel VBA (5 Methods)**