How to Convert Numbers Stored as "Text" to Numbers in Excel

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.

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.

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.

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.

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

Convert Number Stored as Text.xlsx

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.

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

1 Comment

criss Jan 22, 2024 at 10:18 PM

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 !!!

How to Convert Numbers Stored as “Text” to Numbers in Excel