2

I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet.
The filter I have created is working but not exactly how I want it! The column I am referring to has words such as:

  • Solar
  • Energy
  • Battery

and sometimes may have two words in the cell such as:

  • ENERGY COMPARISON, SOLAR (see image below)
    and these words are separated by a comma ,

The formula I created recognizes the single words. However it does not bring over the words that belong to a cell with multiple words.

The example formula I am using that works is this:

=filter('Form responses'!A:A,'Form responses'!I:I="Solar")

How do I update it to also capture the multiple words?

enter image description here

enter image description here

Thank you! :)

  • 1
    Hi Leanne, welcome to SE! I'm sure we can help you, but unfortunately your question isn't sufficiently clear to me to help. Would you be able to a) mention the formula/filter you've tried so far, b) potentially share copy of the sheet you're working on with any irrelevant and sensitive data removed or c) add example input output like for example in this well-written question: webapps.stackexchange.com/questions/54928/… Lastly, please explain "I am not va basic user" and "word/filter". – Cornelius Roemer Sep 26 at 12:56
  • 1
    @Leanne You are looking for =regexmatch with a regex like this one SOLAR|SOLAR & ENERGY where | means OR. You can update your question with more details/data as @cornelius suggest – pjmg Sep 26 at 13:16
  • Thanks all. The formula I have used is this: – Leanne Sep 26 at 23:53
  • 1
    I cannot provide a copy of the sheet as i am not the owner and due to privacy but if this is not clear then I can create a new one showing what I have done :) Thank you so much for responding all – Leanne Sep 26 at 23:56
  • 1
    Hi Leanne, please use the edit button to move that information into the body of your question. Thanks! – jonsca Sep 28 at 0:06
1

Following the OP's Edit things are quite clearer.


Using the FILTER function

=FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar"))

Extra tip
If you were to use the | (pipe symbol) in the FILTER function which stands for OR, then the formula would change to:

=FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar|Energy Comparison"))

But you don't really need to because the first condition (Solar) already includes all text that has the word Solar.


Using a QUERY

=QUERY('Form responses'!A:I, "select A where I matches '.\bSolar\b.' ",0)

As before, it is not necessary to use the | (pipe)

=QUERY('Form responses'!A:I, "select A where I matches '.\bSolar\b.|.\bEnergy\b.' ",0)

  • Have you tried the solution offered? Did it work for you? If not we should try and find out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Oct 2 at 9:55
0

Note that: results given by the following provided formula are highly dependent on the way you have built your form.
If for any reason it does not work for you, please -as mentioned by others- share a link to a test spreadsheet with dummy data (John for Mark, Mr Green for Mr Brown)
.


The query formula using a case-insensitive regular expression (regex) for your search will give the results you want.

=QUERY('Form responses'!A2:C200, ""select * where lower(A) matches 
       '.\bsolar\b.|.\benergy\b.'")

Brief explanation (of what the query "says")

We assume your data starts at column A, row 2, in the spreadsheet Form responses ('Form responses'!A2:C200)

Choose the range A2:C200 and return all the columns wherever -in your search within column A- you find results matching the case-insensitive words solar or SOLAR or even sOlaR or energy or Energy etc, no-matter how many or what characters are before or after them.

  • *: everything (all columns in the range)
  • lower(A): make characters case-insensitive
  • .*: . any character, * any number of times
  • \b: word boundaries (prevents from matching subsolar or solarized)
  • |: (pipe) or

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.