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
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:
- 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. It sounds like you'll have to look into other options.
Excel's Number Formatter and Error Checker
Converting text strings to numbers only requires two clicks:
- Find and select all the cells that have text numbers in them.
- In order to convert to a numerical format, you must first click the warning icon.
Change the format of the cell to turn it into a numeric value.
One more speedy method of converting textual numerical values to numbers is as follows:
- Choose the cells that have numerical data formatted as text.
- From the drop-down list labeled "Number Format," accessible via the Home > Number > General or "Number"
Note There are cases where this strategy will not be useful. Example: if you format a cell as Text, enter a number, and then change the cell format to Number, the cell will still be formatted as text.
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 that Paste Special converted to text:
- Simply follow the steps above to change the format of the cells containing text and numbers to General.
- 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
- 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
- The Paste Special dialog box can be accessed by selecting Values under the Paste heading and Add under the Operation heading.
- 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.
It's another method of turning text into numbers in Excel without the use of 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:
- Make sure the General format is selected for the cells you want to change into numbers.
- To convert text into columns, go to the Data tab, Data Tools group.
- 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
Formula for transforming words into numbers
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:
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 Formulate a date from a string.
The VALUE function doesn't just work with text-based numbers; it can also convert text-based dates.
Case in point:
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:
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:
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.
See How to Extract a Number from a String in Excel for More Information.
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.
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.
Tip Use the Paste Special function to swap out formulas for their corresponding values if you'd prefer to work with the results as values rather than formulas.
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.
If you liked this, you might also like
To convert 5 milliliters to ounces, simply multiply the amount by the number of milliliters in an ounce. It's important to remember that 1 ounce is equal to exactly 1000 milliliters. Simply multiply the amount in milliliters (ml) by zero to get the equivalent in fluid ounces (fl oz). Simply take
The When someone is measuring a length, the question of how to convert feet to meters inevitably arises in our minds. But before we get too far into the feet-to-meters conversion, let's figure out the history and context of the two units. Don't worry, we'll explain the concept of these
WebP images can be converted to JPG in several ways: convert webp images to jpegs online utilizing the operating system-provided Paint (Windows) or Preview (Mac OS), installing a third-party image editor like Adobe Photoshop WebP is Google's replacement for the popular JPEG, GIF,
Free online MOV to MP4 conversion is now possible with this handy tool. Movavi's desktop converter is great for converting large files. Check out the desktop appLook into the computer program Help CenterHelp Desk How-tos Free Online Tool to Convert MOV to MP4 in a