Formulas and other methods for converting text into numbers in Excel

The Excel Convert to Number error checking option, along with formulas, mathematical operations, Paste Special, and more, are just some of the methods demonstrated in this tutorial.

It's possible for values to appear in Excel worksheets that look like numbers but actually cause mathematical errors when added together, multiplied, or calculated. Common causes include textual representations of numerical values. Microsoft Excel is often able to automatically convert numerical strings into numbers when imported from other programs. But sometimes people forget to change the number format to something else, and that can lead to a whole host of problems in your spreadsheets. In this guide, you'll learn how to parse strings and extract their "real" numerical values.

Finding Numbers in Text in Excel

The built-in error checking tool in Excel flags potentially incorrect cell values. This is denoted by a tiny green triangle in the upper left corner of a cell. When an error-marked cell is selected, a warning symbol with a yellow exclamation point appears (see image below for an example). The number in this cell is formatted as text or preceded by an apostrophe; hover over the symbol to see a warning from Excel.

When numbers are entered as text, the error indicator does not always appear. However, there are other visual cues for textual numerals:

Numbers Textual quantities (also called "strings")

• Typically oriented to the right
• The Status Bar displays Average, Count, and SUM if multiple cells are selected.

• Normally oriented to the left
• Multiple selections result in the Status Bar only displaying Count.
• In many instances, though not all, the Text format is what's shown when you click the Number Format button.
• The formula bar could have a missing or incorrect leading apostrophe.

You can compare the textual representations of numbers on the right with the actual numbers on the left in the image below:

Excel's built-in text-to-number converter tutorial

Several methods exist for converting text to numbers in Excel. We'll go over each of them below, starting with the quickest and easiest. Please don't give up if the simple methods don't work for you. Not even the greatest obstacles are insurmountable. The only option is to keep looking for solutions.

Excel's Number Formatter and Error Checker

Converting text strings to numbers only requires two clicks:

1. Find and select all the cells that have text numbers in them.
2. In order to convert to a numerical format, you must first click the warning icon.

Done

Change the number format in the cell to turn text into numbers.

One more speedy method of converting textual numerical values to numbers is as follows:

1. Choose the cells that have numerical data formatted as text.
2. From the drop-down list labeled "Number Format," accessible via the Home > Number > General or "Number"

Paste Special lets you convert text to numeric format

This method of converting text to number has more steps than the others, but it almost always succeeds.

Here's how to fix numbers pasted as text by using Paste Special:

1. Simply follow the steps above to change the format of the cells containing text and numbers to General.
2. In other words, just copy a blank cell. One of two methods will get you there: either press on a chosen cell or Ctrl C use the context menu to copy by right-clicking
3. Simply copy the cells you want to turn into numbers, right-click them, and select Paste Special. Alternately, you can use the Type in "Ctrl Alt V" shortcut
4. The Paste Special dialog box can be accessed by selecting Values under the Paste heading and Add under the Operation heading.
5. Click OK

If you did it right, Excel will recognize your values as numbers and switch the default alignment from left to right.

Text to Columns is a string-to-number converter.

Excel users have another option for making text numeric without using formulas. A multi-step procedure, the Text to Columns wizard is best used for its intended purpose of converting text into columns in a table. In the first step, you click the Finish button to carry out the text-to-number conversion:

1. Make sure the General format is selected for the cells you want to change into numbers.
2. To convert text into columns, go to the Data tab, Data Tools group.
3. Choose Delimited as the Original data type in the first step of the Convert Text to Columns Wizard, then hit Finish.

That settles the matter

Use a formula to transform text into a numerical value.

To this point, we have covered how to use Excel's in-built tools to convert text to numeric form. A formula can be used to speed up the conversion process in many cases.

Formula 1 Excel function to transform text to numeric format

The VALUE function in Microsoft Excel is used to convert text to a numeric value. Both a string of text within quotation marks and a reference to a cell containing the text to be converted are valid inputs for the function.

Unlike any of the other methods, the VALUE function is able to decipher a number that is surrounded by some "extra" characters.

Using the VALUE formula, for instance, you can enter a number followed by the currency symbol and a thousand separator:

=VALUE("$1,000")

=VALUE(A2)

Enter the formula in the first cell, then drag the fill handle to copy the formula down the column to convert a column of text values.

See the VALUE formula for details on how to quantify text.

Formula 2 Put a date on a string.

Dates represented in text strings can also be converted by the VALUE function, in addition to text-numbers.

Case in point:

=VALUE("1-Jan-2018")

Or

=VALUE(A2)

Given that A2 contains text and the current date and time

When using a VALUE formula, Excel will automatically return a serial number that represents the current date. You need only apply the Date format to the formula cell for the result to look like a real date.

To get the same result, you can use the DATEVALUE function:

=DATEVALUE(A2)

How to Convert Text to Dates in Excel for More Details

Formula 3 Parse a string and return a number

You can use the VALUE function to get a number out of a string of text when you use one of the Text functions like LEFT, RIGHT, or MID.

For instance, this formula can be used to extract the last three characters from the contents of cell A2 and return them as a numeric value:

=VALUE(RIGHT(A2,3))

Our formula for converting text to numbers is demonstrated in the following screenshot:

All calculations with the extracted values will fail if the RIGHT function is not wrapped in VALUE. The exact form of the result is a numeric string, which reads like text.

How to Get a Number Out of a String in Excel for More Details

Mathematical operations to convert Excel strings to numbers

In Excel, you can also easily convert a text value to a number by performing a simple arithmetic operation that does not alter the original value. Seriously, what could that be Such operations as zero addition, multiplication by one, and division by zero are examples.

=A2+0

=A2*1

=A2/1

Excel may format the final results as Text if the input values were in a Text format to begin with. You can tell that because the formula cell numbers are aligned to the left. To resolve this, ensure that the formula cells are formatted in General.

This is how you use Excel's formulas and built-in features to transform text into numeric form. I appreciate your time and interest and look forward to your return to our blog next week.

Other Topics You Might Dig: