Adaption of formula to return the second number in a string

[dpotta]

Hello

Where there is an entry in Cell A3 like "10th to 14th Floors" I am using the formula below to return the first numeric value from A3, in this case "10"

=LOOKUP(50,--("0"&MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0 123456789")),ROW($1:$10))))

This works just fine, but I have been trying to adapt it to find the second numeric value, which in this example would return "14". I have tried using the character number for the first numeric value as the start number in searches, but can't quite make it work!

Any suggestions on how to would be welcome - or a new approach (VBA?) for finding the second numeric value in a string.

Obviously, as shown in the formula, the numbers I am searching for are not always single digit numbers.

Thanks in advance for any help you can offer

Dave

 

[jim may]

Should the answer show in a single cell as "1014", or something else. Please clarify!!

 

[dpotta]

Apologies. It should be in 2 cells. So, the first formula (the one I already have is in R2 and the second in S2

 

[Jonmo1]

Try

=LOOKUP(50,--("0"&MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789",FIND(" ",A3))),ROW($1:$10))))

However, I think this may be more complicated than it needs to be.

Is the string you posted a consistent sample of any string that may appear in A ?
It's always "##th to ##th Floors" ?
But the length of the number may vary, and the th may be nd rd st ?

 

[dpotta]

Thanks Jonmo: The string is not always consistent!

Your formula seems to work well - I had come up with a far longer one that worked in the meantime - searching for the " " seems much better

 

[Jonmo1]

You're welcome.