Table of Contents
This is a very simple tip, but it can be very useful in certain situations.
If you have a string that contains a number with a decimal point, and you want to extract that number as a floating point value, you can use the following simple trick:
First, convert the string to a number using the Val function. This will strip off any non-numeric characters:
Dim s As String
s = “123.45”
Dim n As Double
n = Val(s)
Next, divide the number by 10 to the power of the number of digits after the decimal point:
n = n / 10^(Len(s) – InStr(s, “.”))
The result, n, will be the floating point value 123.45.
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.