Table of Contents
The process of extracting a decimal number from a string using Excel involves utilizing the built-in functions and formulas of the software. This can be done by using the “MID” or “LEFT” function to locate the decimal point and extract the numbers before and after it, respectively. Additionally, the “VALUE” function can be used to convert the extracted text into a numerical value. By following these steps, a decimal number can be easily extracted from a string in Excel.
Excel: Extract Decimal Number from String
You can use the following formula to extract a decimal number from a string in Excel:
=MID(A2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")), MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
This particular formula will extract the decimal number from the string in cell A2.
For example, suppose cell A2 contains the following phrase:
- She bought 12.52 pounds of fruit
This formula will return 12.52 as a result.
The following example shows how to use this formula in practice.
Example: Extract Decimal Number from String in Excel
Suppose we have the following list of strings in Excel:
Suppose we would like to extract only the decimal numbers from each string.
We can type the following formula into cell B2 to do so:
=MID(A2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")), MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
We can then click and drag the formula down to each remaining cell in column B:
Column B now contains only the decimal numbers from each of the corresponding strings in column A.
How This Formula Works
=MID(A2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")), MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Here is how this formula works:
First, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″)) returns the position of the first number in the string.
Then, MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT(“1:”&LEN(A2)))),0)) returns the position of the last number in the string.
Then, we use the MID function to extract all of the characters between these two positions in the string.
The end result is that we’re able to extract only the decimal number from the string.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: