How to get the Decimal (REAL) number Only in an st

问题:

Help Needed: How to get the Decimal (REAL) number Only in an string in using an Excel formula or VBA? I have in "column A" a string with just one decimal number on it. I want to extract that decimal (REAL) number ONLY but it is extracting the first number on the string. See below for details...

Current Situation:

I am using on Column B the Formula:

=LOOKUP(9.9E+307,--LEFT(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A1&"1023456789")),999),ROW(INDIRECT("1:999"))))

Column A                                                        |  Column B
"Some text"... The Value of Project 456 is 12.56 ... more text. | 456
Some Text"... Project 459 value is 13.5 ... "more text"         | 459

Desired Situation:

I want to get the decimal (REAL) number ONLY out of the string and ignore the numbers that doesn't contain decimals from Column A. Example:

Column A                                                        |  Column B
"Some text"... The Value of Project 456 is 12.56 ... more text. | 12.56
Some Text"... Project 459 value is 13.5 ... "more text"         | 13.5

Any help needed is appreciated, could be an excel formula or VBA solution.

Thank you!

回答1:

Try this UDF pasted into a standard public module code sheet.

Function realNum(str As String, _
                  Optional ndx As Integer = 1)
    Dim tmp As String
    Static rgx As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    realNum = 0

    With rgx
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
        .Pattern = "[0-9]{1,9}\.[0-9]{1,9}"
        If .Test(str) Then
            realNum = CDbl(.Execute(str)(ndx - 1))
        End If
    End With
End Function

Note that while it defaults to the first occurrence (e.g. B2), you can apply an optional parameter to get the second, third, etc. (e.g. B3).

回答2:

This Array formula will sum all the number that have . in them, per stirng. If only one per string then it returns just that one:

=SUM(IF((ISNUMBER(--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW($1:$25)*99-98,99)))) * (ISNUMBER(FIND(".",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW($1:$25)*99-98,99))))),--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW($1:$25)*99-98,99))))

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode: