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:
- 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:
- 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 number format in the cell to turn text into numbers.
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 pasted as text by using Paste Special:
- 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.
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:
- 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
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:
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:
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.
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.
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.
Other Topics You Might Dig:
If you're thinking about taking a trip to the United States, you might consider exchanging some of your money into U.S. dollars, which is the official currency of the country. The international symbol for the currency is USD.USD is also the official currency in a few other countries, including Ecuador
If you're considering a journey to the United States, it might be beneficial to convert some of your money into U.S. dollars, which is the official currency of the country. The internationally recognized symbol for this currency is USD.Additionally, USD serves as the official currency in Ecuador and El
Utilize our inch-to-fraction calculator to effortlessly perform conversions between inch fractions, decimal values, metric measurements, and feet. Effective Techniques for Calculating Inch FractionsInches can be represented as fractions or decimals. When dealing with inch fractions, it is vital to
Please enter the necessary values below to convert kilowatts [kW] to British thermal units per hour [Btu/h], or the other way around.Description: A kilowatt (symbol: kW) is a unit of power within the International System of Units (SI). The watt, after the Scottish inventor James Watt, serves as the base