How to Convert Numbers Stored as “Text” to Numbers in Excel: 4 Methods

Method 1 – Using the 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:

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

The values will be left aligned, and the warning sign will be removed.

Method 2 – Applying the 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.

• We 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. Drag the Fill Handle icon down to quickly apply the formula to the remaining cells.

Method 3 – 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:

1. Select the cells containing text-formatted numbers.
2. Go to Home tab > Number group > Number Format drop-down > General.
   
3. Click on any empty cell and press Ctrl+C. Or, right-click and choose Copy from the context menu.
4. 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.
5. In the Paste Special dialog box:
   • Select Values in the Paste section;
   • Choose Add in the Operation section;
   • Click OK.

   

This will convert all the selected cells into Number format.

Case 2 – Multiply 1 to the Text-Formatted Numbers

This method is beneficial 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:

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

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.

Method 4 – 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:

1. Choose the column containing the numbers formatted as text.
2. Go to the Home tab > Number group > Number Format drop-down > General option to set their format to General.
   
3. Go to the Data tab > Data Tools group > Text to Columns.
   
   This will open the Convert Text to Columns Wizard.
4. 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 they are in the same column for this process to work effectively.

Method 5 – Using Arithmetic Operations

Suppose you have text-formatted numbers in the range C6:C10.

1. Select cells containing text-formatted numbers that you want to convert.
2. Go to Home tab > Number group > Number Format drop-down > General.
   
   
3. 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. Drag the Fill Handle icon down to apply the formula to the remaining cells quickly.
   

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

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

• Achieve the same outcome using the DATEVALUE function:

=DATEVALUE(C6)

Download the Practice Workbook

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