1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by PradeepR, May 18, 2017.

  1. PradeepR

    PradeepR New Member

    Messages:
    3
    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.
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    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.
    PradeepR likes this.
  3. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,150
    upload_2017-5-20_1-4-24.png

    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 likes this.
  4. PradeepR

    PradeepR New Member

    Messages:
    3
    Thanks Luke.
  5. PradeepR

    PradeepR New Member

    Messages:
    3
    Thanks Bosco.

Share This Page