In this Excel tutorial, we will discuss various ways to convert text to number format, including using Error Checking, Paste Special, Text to Columns and similar features, as well as using mathematical operations, changing cell format, and using the Power Query Editor. We’ll also solve the problem of errors as result of numbers being entered as text.
Why Convert Text to Number Format in Excel?
When numbers are stored as text, you can’t use any mathematical operations or statistical and lookup functions on them. Converting these values to numbers also enables you to compare or sort them, and create charts to visualize the data.
Download Practice Workbook
How to Convert Text to Number in Excel
Suppose we have the dataset below containing the Names and Ages of some students. The age of the students is stored in text format. We’ll convert these text values to numbers using 7 different methods.
Method 1 – Using the Error Checking Option to Convert Text to Number
The easiest way to convert text to number format is to use the Error Checking option, which appears as warning icon in cells where Excel has detected there may be an issue with the cell contents.
Steps:
- Select the cell where the Error Checking icon appears.
- Click on the icon and select Convert to Number from the drop-down list.
The text value will be converted into a number.
Method 2 – Changing the Cell Format to Convert Text to Number
We can easily change any cell format as desired from the Home tab.
Steps:
- Select the cell range to convert.
- Go to the Home tab.
- Click on the Cell Format drop-down and select Number.
The specified cells are converted to Number format.
Method 3 – Using the Paste Special Feature to Convert Text to Number
We can use the Paste Special feature to paste copied data with various formatting or calculation options.
Steps:
- Select any blank cell and press Ctrl + C to copy it.
- Select the desired output cell and right-click on it.
- Select the Paste Special option from the context menu.
- In the Paste Special box that opens, select Values as the Paste option and Add as the Operation option.
- Click OK to convert all the specified values to numbers.
Method 4 – Using the Text to Columns Feature to Convert Text to Number
The Text to Columns feature is usually used to split data in a single column into multiple columns. However, this feature can also be used to convert text to number in Excel.
Steps:
- Select the cell range to convert.
- Go to the Data tab >> Select Text to Columns.
- Click Next in the dialog box that opens.
- Select General as Column data format and then click on Finish.
The values in the specified range are converted to number format.
Method 5 – Using Mathematical Operations
We can add 0 to a text value or multiply it by 1 to convert it into a number in Excel. Here we will add 0 to the text value to convert it to a number.
Steps:
- Use the following formula in Cell D5:
=C5+0- Press ENTER.
- Drag down the Fill Handle tool to copy the formula to the rest of the cells.
As can be seen from their right-sided alignment, the cells in column D have been converted to number format.
Method 6 – Using an Excel Function
The VALUE function will convert a text value to a number. However, if the text data contains a blank space or non-printable characters, we’ll need to combine it with the TRIM and CLEAN functions to achieve the desired result.
6.1 – Using the VALUE Function to Convert Text to Number
The VALUE converts any format such as text, date or currency to number in Excel. The syntax of this function is as follows:
Click image to enlarge
- Use the following formula and copy it to the rest of the cells:
=VALUE(C5)
6.2 – Combining the VALUE, TRIM & CLEAN Functions
If the data contains blank spaces or non-printable characters, we can combine the VALUE, TRIM and CLEAN functions to convert the text to numbers.
The syntax of the TRIM function is as follows:
Click image to enlarge
- Use the following formula to convert text to a number:
=VALUE(TRIM(CLEAN(C5)))
Method 7 – Using the Power Query Editor to Convert Text to Number
The Power Query Editor is a useful tool to convert any data type to a number. Use this method when you are converting a large dataset.
Steps:
- Select the whole dataset.
- Go to the Data tab and click on From Table/Range.
Our Age column is in Text format.
Click image to enlarge
- To change the format, click on the mark button and select Whole Number.
The column will change to number format.
- Close & Load this dataset to use it in your worksheet.
Click image to enlarge
How to Fix Convert to Number Errors in Excel?
When you enter a number in text format, Excel may show an error.
To fix this issue:
- Select the desired cell range.
- Click on the Error Checking option.
- Select Convert to Number.
The text value will be converted into a number.
Things to Remember
- Make sure that the data you are trying to convert is in a consistent format. Inconsistent formats might lead to errors during conversion.
- Before making changes to a large dataset, create a backup or duplicate of your data in case something goes wrong during the conversion process.
Frequently Asked Questions
1. How do I convert multiple columns at once?
Use the methods above, such as applying formulas or functions.
2. Are there any keyboard shortcuts to convert text to numbers?
No, Excel doesn’t have a built-in keyboard shortcut specifically for converting text to numbers. However, you can create a macro with a keyboard shortcut to automate the conversion process.
Convert Text to Number in Excel: Knowledge Hub
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
