Use Excel to Convert Text to Numbers

Introduction

You may not even realize it, but the numbers you see in your Excel spreadsheet might be text representations of numbers, not numeric values. How do you know if this is the case? It's fairly easy to spot text representations of numbers-- if you try to use these values in formulas and get errors or if you find that you can't use them in typical calculations, you'll know that you're not working with actual numeric values. Fortunately, it's easy to use Excel to convert text to numbers. In this tutorial, we'll show you some simple ways to convert a text value to a number in Excel.

Prerequisites

If you'd like to follow along with the examples in this article, be sure you have the latest version of Microsoft Excel installed on your machine.

Using Excel error checking to convert a text into number

One way to use Excel to convert text to numbers is to utilize its error checking feature. Excel provides an option to check for errors which can help you spot cells that contain numbers represented as text. If you see this error indicator displayed near cells in your spreadsheet, you can use the error checking functionality to switch the text representations to actual numerical values.

Here's how to do it:

  • First, select the cells that contain the text representations that you want to convert.

  • You'll see a small diamond-shaped warning icon. Click it to see the Error Checking dialog box.

  • Select the option named Convert to Number to complete the conversion process.

![Screenshot of the excel error checking]()

If you want to handle text-to-number conversions this way, you'll need to make sure error checking is enabled in your Excel installation. Go to your File tab and then select the Options button. You'll get a pop-up dialog box like the one shown below. Look for the option that enables background error checking and check the checkbox next to it.

![Screenshot of the excel error checking setup]()

Using Text to Column to convert the text into numbers

You can also use the Excel Text to Columns command to convert your text values. Let's walk through the steps of the process:

  • In your spreadsheet, select the column of cells you'd like to convert. You can do this by clicking the column letter at the top of the spreadsheet. Then go to your Data tab, where you'll see a 'Data Tools' group that contains the 'Text to Column' button.

  • When you click this button, you'll see a dialog box with a Text Wizard. Typically, the wizard would ask you whether the text is delimited. Just make sure that the Delimited checkbox is checked and then click the Next button.

  • The next step asks what type of delimiters the data contains. Uncheck all of the boxes and proceed to the last step.

  • When you see a choice of column data formats, select General. Finally, click the Finish button if it's enabled, and the conversion is complete.

![Screenshot of the text to column result]()

Using the Excel value function

The Excel value function offers yet another method for transforming the value of a text into a number. This function is particularly useful for pulling numeric values from more complex text strings.

You can see an example of the value function at work in the screenshot below:

![Screenshot of the excel value function]()

In this example, the contents of the cell A1 will be converted into the numeric value '12345'.

Conclusion

If you need to perform calculations or use formulas on numbers in your Excel spreadsheet, you'll need to ensure that those numbers are actual numeric values and not text representations. Excel provides multiple ways to transform the text representation of numbers into their numeric counterparts. In this article, we showed you how to use Excel to convert text to numbers with three different methods. With these instructions to guide you, you'll be able to convert data types in your own Excel spreadsheets.

An innovative marketing agency specializing in marketing tactics designed to produce positive results.
Office Address:
5541 McNeil Dr
Building A
Austin, TX 78729
(628) 333-3450

Email Us