Find first numeric value in a string

[Mgc26133]

Hello.

I'm not sure if this was asked before, I tried to search but couldn't find a thread. Apologies if it's asked before:

So I have a range of "complicated" data, in which I'm trying to find the 1st numeric value. For this I'm currently using a nested SUBSTITUTE to convert all numbers to 0, then a FIND to find the 1st zero, then a LEFT to get the number based on the FIND result. This does the job but it feels too convoluted, and looks kind of ugly. Is there a simpler way to accomplish this?

Either the first number or it's position. Both works for me.

[John Vergara]

Hi Mgc26133

Guessing a little, if you have the text in H9, try:

=MID(H9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},H9&1/17)),1)  

If not, post an Excel file showing us what do you have and what do you want to get. Blessings!

[Mgc26133]

No need, this is exactly what I want!

Many thanks !!

Quick question though.. What does "&1/17" do there?

[John Vergara]

Hi again!
1/17 is a pandigital number (the result contains all the digits (0 to 9)). This concatenation to original number avoid errors to the FIND part. Blessings!

[Mgc26133]

Brilliant!