You Need a Challenge Every Now & Then

***Update 12/01/20*** I was asked if there was a way to do this same type of Query but look for specific text in a cell that may have more than just what you are looking for… So here’s the scenario: A Google Form is being used for reflection after PD. It is a single Form being used across the entire district where there were multiple sessions and a variety of presenters. Instead of combing through the Sheet’s data to find a session you did or using a filter which might affect another viewer of the Sheet; is there a way to pull the data for a specific presenter into its own tab, keeping in mind that sometimes a presenter might be co-facilitating a session with another presenter? I did some digging… here’s what I found: the gist is using the term “matches” and inserting the characters “.*” in front and behind the specific text you’re looking for. It would look like this:

=QUERY(IMPORTRANGE(Google sheet ID“,”Google tab name!column letter:column letter),”Select * Where Col# matches .*text.*‘”)

The other day I received just that, a challenge.


Basically, our Kindergarten program is half-day; the teachers have an AM & PM class. They wanted a Form to collect information but depending on a selected answer, they wanted the information to go into one sheet versus another.

At first, I thought this was simple. Input an “IF” function to designate whether the information in a particular row should go to an AM or PM sheet. The problem with this course of action, data which doesn’t meet the “IF” condition would leave a row blank. Example for an AM sheet:

Timestamp AM or PM? Name: Score: Notes:
05/26/16 08:13 AM Tommy 21 Vowel-Consonant-Vowel trouble
05/27/16 09:12 AM Timmy 14 Decoding issues

So I had to search and search and search. I went to a variety of Google Forms forums, YouTube tutorials, and the Google spreadsheets function list. After a little while and no definitive answer, I started to try things out on my own. Eventually, I discovered a solution!

=QUERY(IMPORTRANGE(“Google sheet ID“,”Google tab name!column letter:column letter),”Select * Where Col# = ‘text‘”)

Below are some resources (Tutorial video, embedded Form, and Sheet)

What is a challenge you recently overcame? Please reply in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *