Extract Numbers from Cell Convert VLOOKUP formula to VBA?

[Joyner]

Hello,

I am trying to use VBA to extract numbers from a cell that contains a sentence that always contains one instance of a number (only number) like "80 percent" (not "80%") or 80.5 percent and I want to extract the number (80.5, 80, etc) as a string.

I have try every function I can find in my searches - there are several posted by users:

=ExtractNumber(A1)
=extractNums(A1)
=TextNum(A1, 0)
=extract_nums(A1)

Maybe I am using them wrong but none of them return the number from the text (I Dim sPercent as String, then use sPercent = ExtractNumber(A1) for example - where A1 is the cell with the sentence).

I did find this formula that returns exactly what I want:

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

So I tried to use it in VBA like:

sPercent = Application.WorksheetFunction.VLookup(99^99,--("0"&MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),ROW($1:$10000)))).Value

But that does not work. Can someone help me with creating the correct VBA version of the VLookup formula (or help with the VBA number extract functions to do what I want)

Thank you for any help and replies.

 

[Joyner]

Got this one line wrong:

sPercent = Application.WorksheetFunction.VLookup(99^99,--("0"&MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),ROW($1:$10000)))).Value

It is supposed to be "Lookup" not "VLookup"

But that still does not work, so what I did was popped the formula in a cell:

Range("AA2").Formula = "above formula here"

then just did

sPercent = Range("AA2")

Not real elegant but it works...

 

[Domenic]

Try...

[font=Verdana]sPercent = Evaluate("=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&""0123456789"")),ROW(1:10000)))")[/font]

 

[Joyner]

Thank you Domenic

That works perfect, Thanks to you I am elegant once again...

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[Rick Rothstein]

Is the number always at the beginning of the text? If so, is there always a space after it? If so...

=LEFT(A1,FIND(" ",A1&" ")-1)

In VB, under the same assumptions above...

PerCent = Split(Range("A1").Value)(0)