When working in Excel, you can quite often have a situation, that values in your spreadsheet look like numbers, but they don’t sum up, multiply or cause errors in formulas. The main reason for this is formatting values as text instead of numbers. In this article, you can find out 5 ways to convert text to numbers in Excel.
- How to recognize, if value is formatted as text?
- Error checking option
- Changing data format from text to number
- Paste special option
- Converting text to number by Text to columns command
- VALUE function
How to recognize, if value is formatted as text?
Verification, if a value in a given cell is formatted as text is possible thanks to the built-in error checking option, which shows warnings of possible problems with data in cells.
This option is visible as small, green triangle located in top left corner of the cell containing potential errors:
After selecting this cell, you can notice small yellow exclamation mark. If you locate mouse cursor on it, below you will find an information about error in given cell. In this case, error indicates that data format in cell seems to be incorrect:
Sometimes, error checking option is not visible, despite the fact that numeric values are formatted as text. In such case you can recognize formatting by other indicators:
|Aligned to right side of the cell||Aligned to left side of the cell|
|After selecting multiple cells, you can find Sum, Count and Average calculated on the Status Bar (bottom bar in Excel)||After selecting multiple cells, you can find only Count calculated on the Status Bar (bottom bar in Excel). For Text values, Sum and Average is not calculated|
Error checking option
If cells in your spreadsheet show an error by error checking option explained above, you can change text to columns by two mouse clicks.
Select all cells with error and click on yellow exclamation mark, then select Convert to numbers:
Changing data format from text to number
Another quick way to convert text to number is changing data format in cells.
- Select cells with values formatted as text
- Go to Home tab of the Ribbon and in Number group choose Number from drop-down list:
This method may not work in some cases. For example, if cell is formatted as text, you enter number and change format to number, entered value will be still recognized by Excel as text.
Paste special option
In contrast to above method, changing text to numbers by Paste special option works in almost 100% cases.
To use it, do the following steps:
Select cells with text values and change their formatting to General (Home tab in Excel):
Right click on some empty cells in spreadsheet and select Paste special command – you can also use key shortcut Ctrl + Alt + V:
In opened window with pasting special options, choose Values in Paste area and Add in Operations and confirm by clicking OK:
After this action, values should be automatically aligned to right side of the cell which means, that Excel recognize them as numbers.
Converting text to number by Text to columns command
Fourth way to change text to numbers is Text to column command which can be found in Data tab:
To use this command, select all needed cells and then click on Text to columns command. This tool is used to some other purposes, but to change format from text to column you just need to click Finish:
Excel provides a special function VALUE which allows to convert text values to numbers.
Text is the only argument of this function and this can be reference to a cell, which should be converted to numbers: