Google Query Trim string data

user_18223091 0
Viewed 100

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.

enter image description here

google-query-language spreadsheet google-sheets regex
2 Answers

not within query. use:

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

enter image description here

update:

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

enter image description here

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

enter image description here

user_56326290

Try this:

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

enter image description here

user_155166990
Related