If you end up analyzing large datasets that contain lots of text, being able to search for specific keywords will come in handy. In this tutorial, we will test various queries for finding matches and specific words in Google Sheets. Keep in mind that these conditions are case-sensitive, so ‘New York’ and ‘new york’ will yield different results.
We have already used the “contains” condition in our tutorial for finding specific dates. This is a general condition and will look throughout every cell in the specified columns for cells that contain the matching keywords. Besides numbers, it can naturally look for certain keywords. For our example, we have added a table containing U.S. states’ etymology on one sheet.
We would like to list the states whose names come from the Spanish language. To do so, we identify column D as the column containing the language of origin and then construct the query command as follows:
select A, B, C, D, E where D contains 'Spanish'
Using this, we get the following result:
Our search query managed to list all the states whose language of origin is Spanish. This command also works even if the word is buried inside the cell. As an example, we will run this query inside the QUERY function:
=QUERY(states!A1:F58,"select A, B, C, D, E, F where F contains 'George'")
And our result looks like this:
The “starts with” condition does a prefix match - matching the first few letters with what you have added as a keyword (or keyletters). As an example,
select A, B, C, D, E, F where A starts with 'Mi'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
Gives us:
On the other hand, the “ends with” condition does a suffix match - matching the last few letters with what you have added as a keyword (or keyletters). As an example,
select A, B, C, D, E, F where A ends with 'na'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
Gives us:
The “matches” condition does a regular expression search. For example, this query
select A, B, C, D, E, F where A matches '.*ana'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
Gives us:
The “like” condition admits two wildcards, allowing you to refine your search. The “%” wildcard serves as a placeholder for zero or more characters of any kind, while the “_” wildcard serves as a placeholder for one character, thus allowing you to specify the length of the expected word result. As an example, we will compare the two wildcards. This one
select A, B, C, D, E, F where A like 'India%'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
Will give this as a result:
While this one
select A, B, C, D, E, F where A like 'India_'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
Will give this as a result:
As you can see, the former query looked for words in column A that began with “India”, and listed them regardless of the amount of succeeding characters. The latter query looked for words in column A that began with “India” and then followed by a single character. Since no row included a word in column A that fulfilled such condition.
If you want to combine two or more separate keywords in the same query, there is a specific way of doing so. For our example, we want to list down U.S. state names that originated from either English or Spanish. To do so, we will write the following query:
select A, B, C, D, E where D contains 'Spanish' or D contains 'English'
With this, we'll have to place it in Google Sheets' QUERY function with the format:
=query([range],"[SQL query]'")
And this is the result that we will get:
...before we can start efficiently using this for our analysis. Keyword search is an important part and parcel of customer feedback analysis, which will give us important metrics such as satisfaction rate and net promoter score. A quick look through the principles behind keyword search will expose you to how complex and complicated natural language processing is.
Instead of studying an entirely new field just to gauge customer feedback, consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!
