I’m using the following formula, to successfully retrieve data.
=QUERY(IMPORTRANGE(...);"select Col5, Col1, Col2 where Col2!=''";FALSE)
The data in Col2 are strings that look like this: A thing - something - some other thing
Only the part before the first occurence of - is of interest; but I’m having trouble only returning this part of the string.
When I use the following formula on one of the strings, I get the desired result:
=REGEXEXTRACT("A thing - something - some other thing";"^[^-]*")
I’m not sure how to combine the formula’s however; or if this is even the right way to go about this?
All help is greatly appreciated!
>Solution :
You can create a new column applying REGEXEXTRACT (with the help of BYROW to do it sequentially) and then use that inside the QUERY.
I’ll use LET so it’s easier to see the process:
=LET(imported;IMPORTRANGE(...);
newcol;BYROW(INDEX(imported;;2),LAMBDA(each;REGEXEXTRACT(each;"^[^-]*")))
QUERY({imported\newcol};"select Col5, Col1, Col6 where Col6!=''";FALSE))
I’m assuming that your imported range has 5 columns, so the new one is the 6th; please adapt it if necessary