I'm working with a semi-bulky IMPORTXML formula that needs some modification. Currently the formula looks like this:
=ARRAYFORMULA(IFNA(VLOOKUP( {"Artist", "Release", "Country", "Format", "Released", "Style", "Last Sold", "Highest"}, TRIM(SPLIT(FLATTEN(SPLIT(SUBSTITUTE(SUBSTITUTE("Artist:"&QUERY(REGEXREPLACE(TO_TEXT(FLATTEN( IMPORTXML(CONCATENATE("https://www.discogs.com/sell/release/",A2,"?ev=rb;"), "//div[@class='profile']/*|//div[@class='section_content toggle_section_content']/ul/li"))), "(.*:)", "¤$1"),,9^9)," – ", "¤Release:", 1), "*",, 1), "¤")), ":")), 2, 0)))
This is working just fine, but I'd like it to return cleaner data. The issues I have now are with the "Format" and "Highest" fields. Here is a link to the public, editable sheets:
https://docs.google.com/spreadsheets/d/13SYsJ7upr5hvwQP6JHrms-Dx-ewirzJeHFx_5lOt6ao/edit?usp=sharing
"Format" is often too cluttered with irrelevant infomation, so I'd like to use a REGEXEXTRACT in the formula to pull only the terms (12", 7", LP). I've been using this in a separate cell:
REGEXEXTRACT(I2:I,", ([\d]+"&CHAR(34)&"|LP)"),"-")).
"Highest" should be converted to EUR from USD with GOOGLEFINACE. Also, is it possible to reformat the Last Sold date to 20XX-12-01 style?
Thanks a ton! I hope some kind soul out there can lend a hand!