Extracting numbers from inconsistent text strings

[netguru]

Hi,

I have tried to google for more information and tried to work this out on my own but I couldn't do it.

I am asking for help here please.

I am trying to come up with a formula that can extract the result numbers from the following strings in individual cells.

• <div id=resultStats>1 result<nobr> (0.15 seconds) </nobr>
• <div id=resultStats>About 106 results<nobr> (0.29 seconds) </nobr>
• <div id=resultStats>About 2,320 results<nobr> (0.38 seconds) </nobr>
• <div id=resultStats>About 390,000,000 results<nobr> (0.26 seconds) </nobr>

As you can see, only "<div id=resultStats>" is consistent.

The text "About" may not always shows up and to make things worse, the numbers will be returned with "thousands separator" and the text "results" maybe singular at time.

The numbers in bold are the numbers which I would like to be extracted.

I would really appreciate it if you can help me, I have spent many hours trying to figure this out.

[dilipandey]

Hi Netguru,

Would suggest you to look into below link of our forum:-

http://www.excelforum.com/excel-new-...d-numbers.html

Regards,
DILIPandey

<click on below 'star' if this helps>

[netguru]

Thank you DILIPandey, I have looked at your post but it is too hard for me. I am going crazy...

[vlady]

it's a lookup maybe the old one as Dilip's post above.


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