+ Reply to Thread
Results 1 to 15 of 15

Pulling data from a cell.

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    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?

    Thank you in advance!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    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.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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

  5. #5
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Pulling data from a cell.

    example.xlsx

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

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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))),"")

    Please see attached file.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    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!

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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"

    Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  9. #9
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Pulling data from a cell.

    Thank you very much! Have a wonderful day

  10. #10
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    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.

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pulling data from a cell.

    Quote Originally Posted by L0st View Post
    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))),"")

  12. #12
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Pulling data from a cell.

    Excellent! that's exactly what I needed

  13. #13
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    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?

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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))),"")
    Last edited by AlKey; 08-02-2015 at 11:30 PM.

  15. #15
    Registered User
    Join Date
    01-11-2011
    Location
    Montreal, Quebec
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Pulling data from a cell.

    Everything works perfectly! Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pulling row data by cell text
    By tanelli80 in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 09-17-2013, 03:32 PM
  2. Pulling text data and dollar amount data from same cell
    By micadeli in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2013, 11:21 AM
  3. Pulling a Specific Cell when Pulling a Tabel from Web Based Data
    By Zallen89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2013, 05:39 PM
  4. Pulling data from moving cell
    By jaikin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2009, 10:31 AM
  5. Pulling data from a cell as a variable
    By ste_mulv in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2009, 11:48 AM
  6. Replies: 2
    Last Post: 02-27-2009, 02:01 PM
  7. Replies: 7
    Last Post: 01-03-2006, 05:45 PM
  8. pulling certain data out of a cell
    By D richardson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2005, 02:06 PM

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