# Pulling data from a cell.

1. ## Pulling data from a cell.

Good afternoon, im new here, not sure if this is the best place to ask this question but here it goes

Example of text in cell B1:

:{"trend":"neutral","price":164},"today":{"trend":"positive","price":"+4"},"members":"false","day30":{"trend":"negative","change":"-

i would need to pull out the "164" above into cell B2.

can anyone explain this to me?  Register To Reply

2. ## Re: Pulling data from a cell.

Will it always be in that place, i.e. inside the first {...}, just inside the close bracket? Will it always be 3 digits? If so, you could try this in B2:

=MID(B1,FIND("}",B1)-3,3)*1

The *1 at the end will convert the text value that MID returns into a number - leave it off if you want the text value instead.

Hope this helps.

Pete  Register To Reply

3. ## Re: Pulling data from a cell.

yes this worked perfectly for my b1 cell! Thank you so much!

the next cell i have has the value "1,871" to pull out in the same place.  Register To Reply

4. ## Re: Pulling data from a cell.

In that case you have 5 characters, so change the 3 (twice) to 5 in the formula.

If you have more data then you should give us some examples of the data that you are dealing with, perhaps by attaching an Excel workbook - the FAQ describes how to.

Hope this helps.

Pete  Register To Reply

5. ## Re: Pulling data from a cell.

example.xlsx

Here is my attempt at uploading an example file. Thank you for your patience.   Register To Reply

6. ## Re: Pulling data from a cell.

Enter this formula in C2 and pull it down

=IFERROR(LOOKUP(10^308,--MID(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&1/17,FIND("price",B1))),ROW(B\$1:B\$99))),"")  Register To Reply

7. ## Re: Pulling data from a cell.

Perfect! these work great! would you be able to explain the formula you posted in sections, as to what each function means? so i can decipher how to apply it to other situations?

Thank you!   Register To Reply

8. ## Re: Pulling data from a cell.

The SEARCH function is searching for the text that looks like numbers. The ROW( part is to allow to adjust the number of characters.
The LOOKUP(10^308 is trying to find the numeric value of the position of the first numeric value in the string after the word "price"  Register To Reply

9. ## Re: Pulling data from a cell.

Thank you very much! Have a wonderful day   Register To Reply

10. ## Re: Pulling data from a cell.

one more quick question, how do i ask that formula to pull the value after the 2nd "price" marked in green on my example sheet.  Register To Reply

11. ## Re: Pulling data from a cell. Originally Posted by L0st one more quick question, how do i ask that formula to pull the value after the 2nd "price" marked in green on my example sheet.
This should give you a number after the next "price"

In D2 and copy down

=IFERROR(LOOKUP(10^308,--MID(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&1/17,FIND("price",B1,FIND("price",B1)+4))),ROW(B\$1:B\$99))),"")  Register To Reply

12. ## Re: Pulling data from a cell.

Excellent! that's exactly what I needed   Register To Reply

13. ## Re: Pulling data from a cell.

so sorry for the continuous questions, you have helped me so much today! I will add reputation again when i can in the formula you linked me,

=IFERROR(LOOKUP(10^308,--MID(B7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B7&1/17,FIND("price",B7,FIND("price",B7)+4))),ROW(B\$1:B\$99))),"")

how can i add in a variable to show if the number is positive or negative?  Register To Reply

14. ## Re: Pulling data from a cell.

Try this formula which will add "-" to the negative number.

=IFERROR(LOOKUP(10^308,--MID(B1,FIND("price",B1,FIND("price",B1)+4)+8,ROW(B\$1:B\$99))),"")  Register To Reply

15. ## Re: Pulling data from a cell.

Everything works perfectly! Thanks again!  Register To Reply