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

Method 1 – 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:
Error Checking Feature

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 signwarning sign and select Convert to Number from the list.
    Using Error Checking Feature

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

Using Error Checking Feature

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.

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

Applying VALUE Function

Method 2 – 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.
    Applying General Format
  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.
    Launching Paste SpecialThe 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.

    Add in Paste Special

This will convert all the selected cells into Number format.

Using Paste Special Tool

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.
    Using Paste Special Tool
  6. In the Paste Special dialog box, choose Multiply, and click OK.
    Multiply in Paste Special Tool

Excel will multiply each cell by 1 and effectively convert the text to numbers.

Using Paste Special Tool

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

Method 3 – 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.
    Applying Text to Columns Feature
  3. Go to the Data tab > Data Tools group > Text to Columns.
    Applying Text to Columns Feature
    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.
    Choosing Delimited

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

Convert Number Stored as Text to Number in Excel

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

  1. Select cells containing text-formatted numbers that you want to convert.
  2. Go to Home tab > Number group > Number Format drop-down > General.
    Applying General Format
  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.
    Output after Converting Number Stored as Text to Number in Excel

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.

Achieve the same outcome using the DATEVALUE function:

=DATEVALUE(C6)

Converting Text Formatted Dates into Numbers in Excel


Download 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


<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sishir Roy
Sishir Roy

Sishir Roy, a recent graduate in Civil Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. As an Excel and VBA Content Developer, he has authored 50+ articles, updated 100+, and solved complex Excel VBA challenges. Excelling in troubleshooting and simplifying problems, his love for diverse problem-solving and aiding others is evident in his keen interests in Data Analysis, Advanced Excel, VBA Macro, and Excel Power Query, enriching the project's... Read Full Bio

1 Comment
  1. 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 !!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo