1

I am attempting to grab data from a cell, by using additional data in the cell above it to make sure its correct. Here is the data, each line is put into a cell by the formula.

<total_count>6</total_count>
<games>
<message>
<appid>376210</appid>
<name>The Isle</name>
<playtime_2weeks>4300</playtime_2weeks>
<playtime_forever>23973</playtime_forever>
</message>
<message>
<appid>4000</appid>
<name>Garry&apos;s Mod</name> --------using this
<playtime_2weeks>289</playtime_2weeks> ------to get this!
<playtime_forever>277810</playtime_forever>
</message>
<message>
<appid>578080</appid>
<name>PLAYERUNKNOWN&apos;S BATTLEGROUNDS</name>
<playtime_2weeks>59</playtime_2weeks>
<playtime_forever>8585</playtime_forever>
<img_icon_url>93d896e7d7a42ae35c1d77239430e1d90bc82cae</img_icon_url>
<img_logo_url>2d2732a33511b58c69aff6b098a22687a3bb8533</img_logo_url>
</message>
<message>

The formula I am currently using is

=QUERY(IMPORTDATA(A1)," select Col1,Col2 where Col2 = '2weeks' and Col2 matches 'Garry'")

It outputs <name>Garry&apos;s Mod</name> and I can't select only '2weeks' since that is repeated several times, and for different games.

Additionally, the layout may change at anytime. The Isle is current at the top, but it may change to be at the middle, or bottom of the list at any time. I know if I spent more time on this, I could just use separate formulas to achieve my goal, however I'm attempting to learn how to used combined formulas. Less clutter, more options, and easier to look at.

Any ideas?

Improve this question
3
  • is the htnl code in one cell or across multiple cells in column?
    – user0
    Apr 10, 2019 at 11:03
  • It is across multiple cells in one column
    – Nate.M-C
    Apr 10, 2019 at 23:48
  • Each line on the OP is a row
    – Nate.M-C
    Apr 11, 2019 at 0:35

3 Answers 3

Reset to default
0 

=REGEXEXTRACT(QUERY({{A1:A25; ""}, {""; A1:A25}}, 
 "select Col1 where Col2 contains 'Garry'", 0), 
 ">(.*)<")

0

Improve this answer
17
  • Thank you again, it is much appreciated. As I stated earlier, I'm learning to combine formulas, so I attempted to combine this with our earlier discussion, using IMPORTDATA, however all I got back was query completed with an empty output. Is it just not possible to combine these formulas? Do I need to import the data first, as shown in your post, and then filter the data?
    – Nate.M-C
    Apr 11, 2019 at 1:07
  • paste me the formula pls
    – user0
    Apr 11, 2019 at 1:21
  • =REGEXEXTRACT(QUERY(IMPORTDATA(F5), "select Col1 where Col2 contains 'Garry'", 0), ">(.*)<") I have the link that grabs all the info from above, on F5
    – Nate.M-C
    Apr 11, 2019 at 2:00
  • I meant that IMPORT fx which is F5
    – user0
    Apr 11, 2019 at 2:01
  • 1
    Works perfectly, thank you so very much, this really helps me learn a lot.
    – Nate.M-C
    Apr 12, 2019 at 4:02
0 

=REGEXEXTRACT(INDIRECT("A"&FILTER(ROW(A:A), SEARCH("*Garry*", A:A))+1), ">(.*)<")

0

Improve this answer
0 

=REGEXEXTRACT(REGEXEXTRACT(QUERY(A:A, , 999^99), 
 "Garry(.*)"), "2weeks>(\d+.\d+|\d+)")

0

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.