How to Convert Text to Number in Excel? (Complete Guideline)

In this Excel tutorial, you’ll find ways how to convert text to number format. You’ll learn how to convert text to numbers in detail here using the Error Checking option, Paste Special, Text to Columns, and other similar features, as well as use mathematical operations, changing cell format, and using Power Query Editor. In case you have entered any number as text, it will show an error. You’ll also find a solution to this problem here.

We’ll use Microsoft 365 in this article. However, these methods are also applicable in previous versions of Excel.

Sometimes you may insert numbers as text or import numerical values from other sources which save numbers as strings. Excel may be smart enough to identify these values as numbers. However, sometimes you can not use any mathematical operations, or to use statistical and lookup functions on them. That’s why you need to convert text to number format in Excel in those cases. It also enables you to compare or sort and create charts to visualize data.

Download Practice Workbook

How to Convert Text to Number in Excel?

You can convert text to numbers in Excel by following 7 different methods including using the Error Checking option, cell formatting techniques, built-in Excel features, applying Excel functions, and many more.

Here, you can see a dataset containing the Names and Ages of some students. The age of the students is stored in text format. We’ll try to convert these text values to numbers in Excel.

1. Convert Text to Number Using Error Checking Option

The easiest way to convert text to number format is to use the Error Checking option.

• You just have to select the cell from where you want to remove the error.
• Then, click on the Error Checking option >> select Convert to Number.
• Thus, the text value will convert into a number in Excel.

2. Change Cell Format to Convert to Number

You can change any cell format to your desired one from the Home tab.

To change cell format from text to number, select your cell range >> go to the Home tab >> click on the Cell Format drop-down >> select Number.

3. Using Paste Special Feature to Convert Text to Number

You can use the Paste Special feature to paste your copied data with various formatting or calculation options.

• To use the Paste Special feature for converting text to number, select any blank cell and press Ctrl + C to copy the cell.
• Then, select your desired cell range and right-click on it.
• Next, select the Paste Special option.

• On the Paste Special box, select Values as Paste option and Add as Operation option.
• Finally, press OK and this will convert all the values to numbers.

4. Convert Text to Number by Applying Text to Columns Feature

You can usually use the Text to Columns feature to split and separate data in a single column into multiple columns. However, you can also use this feature to convert text to number in Excel.

• If you want to use this feature to convert text to number, first select your cell range.
• After that, go to the Data tab >> Select Text to Columns.

• Continue pressing Next in the box.

• In the final step select General as Column data format and then click on Finish.

5. Using Mathematical Operations

You can add 0 or multiply by 1 to your text value to convert it to a number in Excel.

Here we will add 0 with the text value to convert it to a number. To do that, you can use the following formula in Cell D5 and then drag down the Fill Handle tool to copy the formula to the rest of the cells.

6. Convert to Number with Excel Function

You can use simply VALUE function if you want to convert text value to number. However, if your text data contains blank space or non-printable characters, you have to combine TRIM and CLEAN functions with this function.

6.1 Convert Text to Number Using VALUE Function

You can use the VALUE function to convert any format such as text, date or currency to number in Excel. The syntax of this function is given below.

Click image to enlarge

You can use the following formula to use this function and copy it to the rest of the cells.

6.2 Combine VALUE, TRIM & CLEAN Functions

If your data contains blank space or non-printable characters, you can combine VALUE, TRIM and CLEAN functions to convert text to number.

The syntax of TRIM function is like below.

Click image to enlarge

You can use the following formula to use these functions to convert text to number.

7. Convert to Number Using Power Query Editor

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.

• To use this tool, select your dataset and then go to the Data tab >> click on From Table/Range.

• You can see that our Age column is in Text form.

Click image to enlarge

• To change the format, click on the mark button and select Whole Number.

• Thus, the column will change to number format.
• After that, you can Close & Load this dataset to use it in your worksheet.

Click image to enlarge

How to Fix Convert to Number Error in Excel?

When you enter a number in text format it may show an error.

To fix this issue, select your desired cell range >> click on the Error Checking option >> select Convert to Number. Thus, the text value will convert into a number in Excel.

Which Things Should You 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. This will help you recover in case something goes wrong during the conversion process.

Conclusion

In summary, Excel offers various methods that convert text to number format. By using Excel features (Error Checking, Text to Column etc.), and functions (VALUE, TRIM, CLEAN), you can quickly transform text formats to numbers. When you insert numbers as text, it shows an error. You can use the Error Checking option to remove this error. Hope you find it helpful.

Frequently Asked Questions

1. How do I convert multiple columns at once?

You can convert multiple columns using similar methods, such as applying formulas or functions to multiple cells or columns simultaneously.

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