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
Recall the formula that we used to extract the decimal numbers from the strings:
=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:
Excel: How to Extract Text Between Two Characters
Excel: How to Extract Text After a Character
Excel: How to Extract Text Before a Character
