Extract numbers from alphanumeric

[PradeepR]

Hello All,

I have a requirement to extract numbers from alphanumeric.
Below are the few possible scenarios:

Residential 1st Charge - Maximum 30%
Debt <£30m - Maximum 4.0x
Vessels over 55 Years - Maximum 45%
Tenants - Minimum 1.05x
Schools - Maximum 60%
Overdraft - Maximum 2 Year
Maximum 1 Year
Maximum 10 Years


From the above, I have to extract the numbers (with decimals) after text ("Minimum" or "Maximum") i.e. 30, 4.0, 45, 1.05, 60, 2, 1, 10 and ignore other numbers which comes before i.e. 1, 30 & 15.

Thanks in advance.

 

[Luke M]

Hello, and welcome to the forum!

Will this work for you?
=VALUE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"imum",REPT(" ",999)),999,999)),"%",""),"x","")," ",REPT(" ",999)),999)))

If you want to preserve trailing 0's, then remove the outer VALUE function.

 

[bosco_yip]

In B1, copy down :

=-LOOKUP(1,-RIGHT(LEFT(A1,MATCH(1,INDEX(-MID(A1,ROW($1:$99),1),0))),ROW($1:$15)))

Regards
Bosco

 

[PradeepR]

Hello, and welcome to the forum!

Will this work for you?
=VALUE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"imum",REPT(" ",999)),999,999)),"%",""),"x","")," ",REPT(" ",999)),999)))

If you want to preserve trailing 0's, then remove the outer VALUE function.

Thanks Luke.

 

[PradeepR]

View attachment 41851

In B1, copy down :

=-LOOKUP(1,-RIGHT(LEFT(A1,MATCH(1,INDEX(-MID(A1,ROW($1:$99),1),0))),ROW($1:$15)))

Regards
Bosco

Thanks Bosco.