Google Query Trim string data

Question

I am using the following data:

cat1-001A
cat1-001B
cat1-001C
dog2-001A
etc.

the query I used is

=query(sheet1!A1:A,"select A where A is like '%cat%'",1)

Using this query pulls data as cat 1-3. Is there a way within the query to trim the text to only return the word cat? I do not want the -1, -2, -3.

so the value returned will be cat.

Why not just use `COUNTIFS()` then. For example `=TRANSPOSE(SPLIT(REPT("cat|",COUNTIF(A:A,"*cat*")),"|"))`? — JvdV, Apr 05 '22 at 08:09
Why `QUERY()`? It's an alternative rather. Usually faster than any regular expression. — JvdV, Apr 05 '22 at 12:52

player0 · Answer 1 · 2022-04-05T09:33:00.057

2

not within query. use:

=INDEX(REGEXREPLACE(QUERY(Sheet1!A1:A, 
 "select A where A contains 'cat'", 1), "-\d+$", ))

update:

=INDEX(REGEXREPLACE(QUERY(Sheet1!A1:A, 
 "select A where lower(A) contains 'cat'", 1), "-\d+.+", ))

=INDEX(REGEXREPLACE(QUERY(Sheet1!A1:A, 
 "select A where lower(A) contains 'cat'", 1), "\d+-\d+.+", ))

edited Apr 05 '22 at 09:33

answered Apr 04 '22 at 22:00

player0

score 0 · Answer 2 · answered Apr 05 '22 at 07:32

0

Try this:

=REGEXREPLACE(QUERY(Sheet1!A1:A, 
 "select A where A contains 'cat'", 1), "\d-\d+.*",)

answered Apr 05 '22 at 07:32

Century Tuna