# Excel 2007 : Extracting Multiple numerical values from a cell

1. ## 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???

2. ## 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.

3. ## 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. ## 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. ## Re: Extracting Multiple numerical values from a cell

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

6. ## Re: Extracting Multiple numerical values from a cell

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

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

#### 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