Extrcting Numbers from an Alphanumeric String

[kramtronix]

Hey guys, I've been racking my brain trying to come up with a formula approach to extracting numbers from alphanumber strings
in some cells of my workbook. I've read the following threads, and while they come close, they do not give me exactly what I need.

Here are the threads:

Extracting Multiple Numbers from String


Extract Text or Number from a Column, Help.


The solutions in these actually work, but the problem is that the number I'm dealing with contains a decimal point. Here is an example
of the cell data:

SEWI Beta Assoc 1.2

The formulas in the threads above render "12." What I need is just plain ole "1" as the result, but "1.2" would work if need be.

Any help is greatly appreciated!

 

[johnmpl]

Hey kramtronix!

Check this UDF, this Extract the Numbers and the point also:

Function ENAP(Rango As Range) As String

Dim Aux$, i&

For i = 1 To Len(Rango)
    If Mid(Rango, i, 1) Like "#" Or Mid(Rango, i, 1) = "." Then
        Aux = Aux & Mid(Rango, i, 1)
    End If
Next i
ENAP = Aux
End Function

God Bless you!

 

[mikerickson]

Perhaps

Function NumberFromString(aString As String) As Double
    If Val(aString) <> 0 Or (aString = vbNullString) Then
        NumberFromString = Val(aString)
    Else
        NumberFromString = NumberFromString(Mid(aString, 2))
    End If
End Function

 

[Rick Rothstein]

Is your number always at the end of the text (set off by a space) as shown in your single example? If so, you could use this formula...

=INT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

 

[kramtronix]

Yep, that's it, Rick! You guys always come thru for me on here in my deperate times of need. LOL