When numbers in Excel are stored as text, calculations and formulas may not work as expected. This issue often arises after importing data from external sources like text files or web pages. Converting these text-formatted numbers back to numeric format is essential for accurate computations.

This guide explores several methods to transform text into numbers in Excel, ensuring your data works seamlessly with formulas and calculations.

Identifying numbers stored as text

At times, values in Excel appear numeric but don't behave like numbers in calculations. Here's how to determine if a value is stored as text:

  • By default, text aligns to the left of a cell, while numbers align to the right.
  • Selecting multiple numeric cells displays Average, Count, and Sum in the status bar at the bottom. If the cells are text, only Count appears.
  • A small green triangle in the top-left corner of a cell indicates an error, suggesting the number is formatted as text.
  • Hovering over the error indicator shows a message: "The number in this cell is formatted as text or preceded by an apostrophe."
  • Summing numbers stored as text results in incorrect totals.

Methods to convert text to numbers

Several techniques can convert text-formatted numbers into actual numbers. We'll cover the most effective methods first:

Convert using the error indicator

When imported data or values with leading apostrophes result in text-formatted numbers, Excel displays a green triangle error indicator. Here's how to use it to convert text to numbers:

Select the cells containing the text-formatted numbers. Click the yellow warning sign that appears next to the selected cells. From the dropdown, choose "Convert to Number".

The selected cells are now converted to numbers.

Changing the cell format

If the error indicator isn't available, you can change the cell format to convert text to numbers:

Select the cells you wish to convert. Navigate to the "Home" tab, and in the Number group, click the dropdown menu. Choose "General" or "Number".

This changes the format of the selected cells. Numbers will align to the right, indicating they're now recognized as numeric values.

If this method doesn't work, proceed to the next techniques.

Using Paste Special to add zero

You can convert text to numbers by performing a simple arithmetic operation that doesn't change the value. Here's how:

Copy an empty cell (which contains zero). Select the cells with text-formatted numbers. Right-click and choose "Paste Special", or press Ctrl + Alt + V. In the Paste Special dialog, select "Values" under Paste, choose "Add" under Operation, and click "OK".

This adds zero to each selected cell, effectively converting the text to numbers.

Using Text to Columns wizard

The Text to Columns feature can also convert text to numbers, especially useful for entire columns:

Select the cells to convert. Go to the "Data" tab and click "Text to Columns" in the Data Tools group.

In the wizard, choose "Delimited" and click "Next". In the next step, ensure "Tab" is selected, then click "Next" again.

In the final step, select "General" under Column data format and click "Finish". This will convert the text to numbers in place.

Applying a mathematical operation

Another straightforward method is to perform a mathematical operation that doesn't alter the value, such as multiplying by 1:

In a blank cell next to your data, enter the formula:

=A1*1

Replace A1 with the cell reference of your text-formatted number. Copy this formula down to apply it to other cells. This will convert the text to a number.

Using the VALUE function

The VALUE function converts text that represents a number into a numeric value:

In a cell, enter:

=VALUE(A1)

Again, replace A1 with the appropriate cell reference. This function can process numbers stored as text, even if they include special characters.

You can also combine VALUE with text functions like RIGHT, LEFT, or MID to extract numbers from text strings.

=VALUE(RIGHT(A1,5))

This formula extracts the last five characters from the text in A1 and converts them to a number.

By using these methods, you can effectively convert text-formatted numbers to numeric values in Excel, ensuring accurate calculations and data analysis.