When Excel treats numbers as text, it can throw off calculations. This often happens when you import data from text files or websites. Let’s look at ways to convert those pesky text-formatted numbers into actual numbers so your formulas work correctly.
Here are a few methods to turn text into numbers in Excel, so your data plays nicely with your formulas.
Spotting Numbers Stored as Text
Sometimes, numbers look right but don’t act like numbers. Here’s how to tell if your data is actually stored as text:
- Text usually aligns to the left side of a cell, while numbers align to the right.
- When you select multiple numeric cells, the status bar at the bottom will show Average, Count, and Sum. If they’re text, it’ll only show Count.
- A green triangle in the top-left of a cell signals an error, meaning it might be text.
- If you hover over that little error icon, it will say something like: “The number in this cell is formatted as text or preceded by an apostrophe.”
- Trying to sum numbers stored as text will give you wrong results.
Methods to Convert Text to Numbers
Here’s a rundown of the best ways to convert text-formatted numbers to real numbers.
Method 1: Using the Error Indicator
If Excel detects a number as text (often from imported data or a leading apostrophe), it displays that green triangle error. Here’s how to use it:
Select the cells with the text-formatted numbers. Click the yellow warning sign that pops up next to your selection. In the dropdown menu, select “Convert to Number”.
The selected cells will immediately switch to numbers.
Method 2: Changing the Cell Format
If you don’t see the error indicator, changing the cell format can do the trick.
Select the cells you want to change. Go to the “Home” tab, and in the Number section, click the dropdown. Choose “General” or “Number”.
This will update the cell format. The numbers should now align right, which means they’re recognized as numbers.
If this doesn’t convert your data, try the next method.
Method 3: Paste Special with Addition
Another quick trick is using Paste Special.
Copy an empty cell (it should contain zero). Select the text-formatted numbers you want to convert. Right-click, choose “Paste Special”, or press Ctrl + Alt + V. In the dialog box, select “Values” under Paste, then “Add” under Operation, and click “OK”.
Adding zero to each cell will convert the text into numbers.
Method 4: Using Text to Columns
The Text to Columns feature can help, especially when dealing with entire columns of data.
Select the cells you want to convert. Head to the “Data” tab and click “Text to Columns” in the Data Tools section.
In the wizard, choose “Delimited”, then click “Next”. In the next step, make sure “Tab” is selected, and click “Next” again.
On the final step, choose “General” under Column data format and click “Finish”. This converts the text to numbers in place.
Method 5: Simple Math
A quick and easy method is to perform a mathematical operation that doesn’t actually change the value, like multiplying by 1.
In a blank cell next to the data, enter the formula:
=A1*1
Replace A1 with the cell that has your text-formatted number. Copy this formula down to apply it to other cells. This should turn the text into a number.
Method 6: The VALUE Function
The VALUE function is designed to convert text that looks like a number into a real number.
In a cell, enter:
=VALUE(A1)
Again, replace A1 with your cell reference. The VALUE function works even if the text has special characters.
You can also use the VALUE function with other text functions like RIGHT, LEFT, or MID to grab numbers from within text.
For instance:
=VALUE(RIGHT(A1,5))
This formula extracts the last five characters from the text in A1 and converts them into a number.
