'Google Query Trim string data

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

regexgoogle-sheetsspreadsheetgoogle-query-language


Solution 1:[1]

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

Solution 2:[2]

Try this:

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

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Diego Sanchez

Related Questions