0

I have a column in my Google Sheet that contains the title of each of our e-blasts from the past 7 years. I want to create a column that provides the "type" of the e-blast, using IF/SEARCH or something similar to find a substring in the first column and return a string in the last column.

We didn't necessarily label each event invitation with the word "invitation," for example — e.g., there were some invitations labeled as "reminder." :(

Basically, I want to search for a set of words like "reminder" and "invitation" and have the cell return the string "Event Invite." But here's where I'm running into trouble: In the same stroke, I need to also search the cell for the substring "e-blast" and have it return "Newsletter," and "follow up" should return "Recap," and so on.

Thanks so so much in advance for any advice!!

PS: Here's what I'm trying, but it returned an error.

=ArrayFormula(IFNA(IFS(SEARCH(”Reminder| Date| Webinar| Conference| Invitation| Invite| Fireside| Session| Fair| Panel”,C4:C)=true,”Event”, 
                        SEARCH(”Newsletter| Blast”,C4:C)=true,”Newsletter”
                        SEARCH(”Apply| Applications”,C4:C)=true,”Call for Applications”
                        SEARCH(”Recap| Recording| Follow”,C4:C)=true,”Recap”)))

Google Sheets screenshot

1

1 Answer 1

0

You may want to take a look at regular expressions. To learn the exact regular expression syntax used by Google Sheets, see RE2.

Here is a simple example.

=arrayformula( 
  ifs( 
    regexmatch(C4:C, "(?i)Newsletter|Blast"), "Newsletter", 
    regexmatch(C4:C, "(?i)Apply|Application"), "Call for Applications", 
    regexmatch(C4:C, "(?i)Recap|Recording|Follow"), "Recap", 
    regexmatch(C4:C, "(?i)Reminder|Date|Webinar|Conference|Invitation|Invite|Fireside|Session|Fair|Panel"), "Event", 
    true, iferror(1/0) 
  ) 
)
3
  • @May - note that (?i) modifier is used there for case-insensitive matching
    – Daniele
    Commented Nov 15, 2022 at 21:57
  • Please ask only one question per post. Commented Nov 16, 2022 at 16:36
  • @doubleunary Got it! Thanks.
    – May
    Commented Nov 16, 2022 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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