+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Extracting Multiple numerical values from a cell

  1. #1
    Registered User
    Join Date
    12-03-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    14

    Extracting Multiple numerical values from a cell

    Hi All is this possible to extract multiple numbers in a cell.
    Ex: If a cell contains "set from 40.4% to -37.3% and the result is ($50,342) as per Raghavan" or "set from -110.4% to -137.3% and the result is $20,342 as per Raghavan".

    I want to extract the percentage value (40.4%) in one cell the result value -37.3% in the other & the result value (-$50,342) in the other CELL???
    Please help
    Last edited by IamHarish; 12-23-2011 at 06:59 PM.

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Etracting Multiple numerical values from a cell

    Try these,

    B1,

    =IFERROR(LOOKUP(9E300,--RIGHT(REPLACE(A1,FIND("%",A1)+1,250,""),ROW(A$1:A$20))),"")

    C1,

    =IFERROR(LOOKUP(9E300,--RIGHT(REPLACE(A1,FIND("^",SUBSTITUTE(A1,"%","^",2))+1,250,""),ROW(A$1:A$20))),"")

    D1,

    =IFERROR(LOOKUP(9E300,--LEFT(REPLACE(A1,1,FIND("$",A1),""),ROW(A$1:A$20)))*IF(MID(A1,FIND("$",A1)-1,1)="(",-1,1),"")

    Copy down all three.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    12-03-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Extracting Multiple numerical values from a cell

    Thanks alot! Thats what I wanted. exactly works for the above example given by me, but I am not able to do that in my file, let me tell you exact cell reference.
    The cell in which data starts from S10 & I have n nomber of rows following it.
    The formula which you said to insert in B1 should be in the cell AY. C1 formula should come in AZ & the D1 in BA cell respectively.

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Extracting Multiple numerical values from a cell

    AY10,

    =IFERROR(LOOKUP(9E300,--RIGHT(REPLACE(S10,FIND("%",S10)+1,250,""),ROW(AY$1:AY$20))),"")

    AZ10,

    =IFERROR(LOOKUP(9E300,--RIGHT(REPLACE(S10,FIND("^",SUBSTITUTE(S10,"%","^",2))+1,250,""),ROW(AZ$1:AZ$20))),"")

    BA10,

    =IFERROR(LOOKUP(9E300,--LEFT(REPLACE(S10,1,FIND("$",S10),""),ROW(BA$1:BA$20)))*IF(MID(S10,FIND("$",S10)-1,1)="(",-1,1),"")

    Copy down.

  5. #5
    Registered User
    Join Date
    12-03-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Extracting Multiple numerical values from a cell

    Super excellent Fantastic. you are just AWESOME! THANKS A LOT

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Extracting Multiple numerical values from a cell

    Glad to hear Harish

    If you are satisfied with solution(s) provided please mark your thread as SOLVED.

    - Go to the first post
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solve
    - Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1