Google Sheets Query - How to return part of String?

Question

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!

Answer 1

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!=''",0))

I'm assuming that your imported range has 5 columns, so the new one is the 6th; please adapt it if necessary