• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract numbers from alphanumeric

PradeepR

New Member
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.
 
Hello, and welcome to the forum! :awesome:

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.
 
Hello, and welcome to the forum! :awesome:

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.
 
Back
Top