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?

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

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

0

  • 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 at 1:07
  • paste me the formula pls – user0 Apr 11 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 at 2:00
  • I meant that IMPORT fx which is F5 – user0 Apr 11 at 2:01
  • 1
    Works perfectly, thank you so very much, this really helps me learn a lot. – Nate.M-C Apr 12 at 4:02
0

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

0

0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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