Sometimes numbers in Excel may not work correctly during calculations if theyâ€™re mistakenly formatted as text. Even though Excel usually converts numerical strings to numbers, sometimes they stubbornly stay as text. To fix this, you can try changing the **Number Format** of the cells to convert them into numbers. However, this might not always work. Even after changing the format to **Number**, you may still face error indicators that can affect your ability to apply these numbers in various calculations.

In this Excel tutorial, youâ€™ll learn how to convert numbers stored as text into actual numeric values using Excel features and functions.

Consider a dataset where numbers are formatted as text (left-aligned) and show the error indicator. I have used the **Convert to Number** option in the error indicator to convert the text-formatted numbers into actual numbers (right-aligned) here. Now we can use these values for calculations in Excel.

**Table of Contents**Expand

## How to Identify Numbers Formatted as Text in Excel

There are several ways to identify numbers that are formatted as text in Excel:

- An error indicator can be shown as a small green triangle positioned in the top left corner of the cell containing the error.

When a cell with an error indicator is selected, a caution sign with a yellow exclamation point is displayed. Hovering the mouse pointer over the sign will provide information about the potential problem, such as*The number in this cell is formatted as text or is preceded by an apostrophe*. - By default, text-formatted numbers are left-aligned in Excel.
- If multiple cells are selected, the
**Status Bar**will only show the*Count*of selected cells. However, the**Status Bar**will show the*Average*,*Count*, and*Sum*if the cells are formatted as numbers. - For text-formatted numbers, the
**Number Format**box may display the**Text**format. However, the**Number Format**can also be in**General**for some cases. - In some instances, there may be a leading apostrophe (â€˜) visible in the formula bar.

## 5 Ways to Convert Number Stored As Text to Number in Excel

Dealing with numbers, that are stored as text in Excel, can be frustrating, but implementing one of these six solutions will help you efficiently convert and manage such data.

Here are 5 possible solutions for converting text-formatted numbers into actual numbers in Excel:

### Using Error Checking Indicator

This method is only applicable if you see an error indicator in the top left corner of a cell like the image below:

The **Error Checking** indicator in Excel is a specialized tool to identify and address potential issues. It can automatically detect cells with numbers formatted as text and provide a quick, user-friendly solution.

To change the format from text to number, follow the steps below:

- Select the cells that contain numbers formatted as text which you want to convert.
- Click on the warning signÂ and select
**Convert to Number**from the list.

As a result, the values will be left aligned and the warning sign will be removed.

### Applying VALUE Function

**The VALUE function** ensures a seamless transformation of text into numerical values in Excel, accepting text strings in quotation marks or references to text-formatted cells. It excels in recognizing numbers with â€˜extraâ€™ characters, such as currency symbols or thousand separators.

Suppose you have a text-formatted number in cell **C6**. To convert it into an actual number, use the following formula:

`=VALUE(C6)`

This formula converts the text string in cell **C6** into its original numerical value. You can then drag the **Fill Handle** icon down to apply the formula to the remaining cells quickly.

### Using Paste Special

The **Paste Special** tool in Excel provides precise control over data pasting. When converting numbers formatted as text, it allows you to selectively paste only the numerical values, ensuring accuracy and efficiency without altering the original formatting.

Here are 2 different cases of using the **Paste Special** tool for converting text-formatted numbers into actual numbers:

#### Case 1: Adding 0 to the Text-Formatted Numbers

To convert numbers formatted as text into numbers using the **Paste Special** tool, follow the steps below:

- Select the cells containing text-formatted numbers.
- Go to
**Home**tab >**Number**group >**Number Format**drop-down >**General**. - Click on any empty cell and press
**Ctrl**+**C**. Or, right-click and choose**Copy**from the context menu. - Select the cells you want to convert to numbers and press
**Ctrl**+**Alt**+**V**. Or, right-click, and choose**Paste Special**.

The**Paste Special**dialog box will appear now. - In the
**Paste Special**dialog box:- Select
**Values**in the**Paste**section; - Choose
**Add**in the**Operation**section; - Click
**OK**.

- Select

As a result, this will convert all the selected cells into **Number** format.

#### Case 2: Multiply 1 to the Text-Formatted Numbers

This method is especially helpful when you quickly need to convert more than one column of text to numbers in Excel.

Follow the steps below to convert text-formatted numbers into actual numbers:

- Choose a blank cell that does not have any text formatting issues.
- Input the number 1 into the cell and press
**Enter**. - Copy the cell by pressing the
**Ctrl**+**C**key. - Next, select the cells that contain text-formatted numbers which you want to convert.
- Go to the
**Home**tab >**Paste**>**Paste Special**. - In the
**Paste Special**dialog box, choose**Multiply**, and then click**OK**.

As a result, Excel will multiply each cell by 1 and effectively convert the text to numbers.

**Note:** This method will remove all formattings from the selected cells.

**Read More: **Number Format Is Not Working in Excel

### Applying Text to Columns Feature

The **Text to Columns** feature in Excel offers precise control for converting numbers formatted as text. By choosing delimiters and guiding the process, it ensures accurate and efficient transformations without altering the original formatting.

Follow the steps below to convert the text-formatted numbers into original numbers using the **Text to Columns** feature:

- Choose the column containing the numbers formatted as text.
- Go to the
**Home**tab >**Number**group >**Number Format**drop-down >**General**option to set their format to**General**. - Go to the
**Data**tab >**Data Tools**group >**Text to Columns**.

This will open the**Convert Text to Columns Wizard**. - In the first step of the
**Convert Text to Columns Wizard**, choose**Delimited**under**Original data type**, and click**Finish**.

This will convert the text-formatted numbers into actual numbers.

**Note:** While using the Text to Columns feature in Excel, if you want to convert specific cells, ensure that they are in the same column for this process to work effectively.

**Read More: **[Solved!] Long Numbers Are Displayed Incorrectly in Excel

### Using Arithmetic Operations

Using basic arithmetic operations like adding zero, multiplying, or dividing by 1, you can convert a number stored as text into an actual number in Excel while preserving the original value.

Suppose you have text-formatted numbers in the range **C6:C10**. To convert them into actual numbers, follow the steps below:

- First, select cells containing text-formatted numbers that you want to convert.
- Go to
**Home**tab >**Number**group >**Number Format**drop-down >**General**. - Next, select any cell and insert any of the 3 following formulas:
`=C6+0`

or,`=C6*1`

or,`=C6/1`

This formula converts the text string in cell**C6**into its original numerical value. You can then drag the**Fill Handle**icon down to apply the formula to the remaining cells quickly.

## How to Convert Date Stored as Text to Number in Excel

To transform a date stored as text into a number in Excel, you can utilize the **VALUE** function. By default, the **VALUE** formula returns a serial number representing the date in Excel. To display the result as an actual date, you need to apply the Date format to the formula cell.

Suppose you have text-formatted dates in the range **C6:C10**. To convert them into numbers, use the following formula:

`=VALUE(C6)`

This formula converts the text-based date in cell **C6** into its numerical value.

Alternatively, you can achieve the same outcome using the **DATEVALUE** function:

`=DATEVALUE(C6)`

**Download Practice Workbook**

## Conclusion

In conclusion, there are various methods to convert numbers stored as text to actual numerical values in Excel, such as using the **Error Checking** feature, changing the **Number Format**, using the **Paste Special** tool, utilizing the **VALUE** function, applying the **Text to Columns** feature, and using the arithmetic operations. Additionally, Iâ€™ve shown how to convert dates stored as text to numbers in Excel using the **VALUE** or **DATEVALUE** function. If youâ€™ve any questions regarding this, feel free to ask in the comment section below or on our forum. Thank you!

## Frequently Asked Questions

### How to Convert Numbers into Text in Excel?

To convert numbers into text in Excel, simply select the cell or range you want to convert. Then, go to the **Home** tab and navigate to the **Number** group. Click on the arrow next to the **Number Format** box and choose **Text**. If you canâ€™t find the **Text** option, scroll to the end of the list.

### How to Fix Number Formatting in Excel?

If youâ€™re facing number formatting issues in Excel, you can easily fix them. Start by selecting the cells that have the problematic formatting. Right-click on the selected cells and choose **Format Cells**. In the **Format Cells** dialog box, go to the **Number** tab. From there, select the desired category, such as **Number**, and click **OK** to correct the formatting.

### How to Fix Auto Formatting in Excel?

To manage auto-formatting preferences in Excel, you can access the **AutoCorrect** dialog box. Click on the **File** tab, then select **Options**. In the **Options** dialog box, choose **Proofing** and click on **AutoCorrect**Â **Options**. From there, go to the **AutoFormat As You Type** tab. Customize your preferences according to your needs, such as converting hyperlinks, including new rows and columns in tables, and filling formulas in tables for calculated columns.

## Related Articles

- How to Convert European Number Format to US in Excel
- How to Convert Percentage to Decimal in Excel
- How to Convert Exponential Value to Exact Number in Excel

**<< Go Back to Number FormatÂ |Â Learn Excel**

Storing numbers as text by default is the most annoying and stupid invention ever created by MS ! Its not the problem to manually convert to numbers, but becomes the problem where huge datasets are used !!!

Let someone from MS do the job MANUALLY and their hands to “fall off” after required insane mouse use !!!