CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulagoogle-sheets-queryimportrange
LePez
LePez

Reputation: 11

Query Google Sheets - how to match partial String

I want to querying all data entries in a Google Sheet that match a partial string in one column. More precisely, the String I query are the initials of one person "AB". Now I want to match all entries that contain either the initials or a combination of the initials of two people: "AB/CD".

This is what I tried so far:

=QUERY(IMPORTRANGE(Overview!X21, "Todo!C2:F"), "select Col2 where Col2 = '.*"&F1&".*'")

F1 is a cell that contains the initials I want to query.

Even though the initials are in the requested column, nothing is returned. If I query:

=QUERY(IMPORTRANGE(Overview!X21, "Todo!C2:F"), "select Col2 where Col2 = 'Initials'")

it works but I don't want to work with regex.

Hope someone can help me solve this mystery.

Upvotes: 1

Views: 5802

Answers (2)

player0
player0

Reputation: 1

the other way would be:

=QUERY(IMPORTRANGE(Overview!X21, "Todo!C2:F"), 
 "select Col2 where Col2 = '"&FILTER(IMPORTRANGE(Overview!X21, "Todo!D2:D"), 
 SEARCH("*"&F1&"*", IMPORTRANGE(Overview!X21, "Todo!D2:D")))&"'")

Upvotes: 1

JPV
JPV

Reputation: 27262

You may want to try

=QUERY(IMPORTRANGE(Overview!X21, "Todo!C2:F"), "Select Col2 where Col2 contains '"&F1&"'")

Upvotes: 2

Related Questions