In a calculated column, how can search find capitalized words?

Question

I am setting up a calendar for notification of professional development events (taking the place of group emails).

In my calendar, I set up a calculated column to search the Summary column for keywords. The problem is it works great if in lower case. If the keyword is capitalized, it is not recognized.

I tried to add the variations, but it still does not work:

=IF((ISNUMBER(FIND({"webinar", "Webinar"},[Summary]))),"Webinar",  
IF((ISNUMBER(FIND({"conference", "Conference"},[Summary]))),"Conference",  
IF((ISNUMBER(FIND({"course","Course"},[Summary]))),"Course",  
IF((ISNUMBER(FIND({"class","Class"},[Summary]))),"Course",  
IF((ISNUMBER(FIND({"workshop","Workshop"},[Summary]))),"Workshop",  
IF((ISNUMBER(FIND({"symposium","Symposium"},[Summary]))),"Workshop",  
IF((ISNUMBER(FIND({"forum", "Forum"},[Summary]))),"Workshop",  
"Other")))))))

This still does find the lower-case keywords at least, but I would love for people to not have to edit.

The calendar overlays depend on this column, so it's important that the search is accurate.

Answer 1

You can use the more versatile function SEARCH which is case-insensitive by default. Your new formula would then look like that:

=IF((ISNUMBER(SEARCH("webinar",[Summary]))),"Webinar",  
IF((ISNUMBER(SEARCH("conference",[Summary]))),"Conference",  
IF((ISNUMBER(SEARCH("course",[Summary]))),"Course",  
IF((ISNUMBER(SEARCH("class",[Summary]))),"Course",  
IF((ISNUMBER(SEARCH("workshop",[Summary]))),"Workshop",  
IF((ISNUMBER(SEARCH("symposium",[Summary]))),"Workshop",  
IF((ISNUMBER(SEARCH("forum",[Summary]))),"Workshop",  
"Other")))))))

SEARCH will find all variations of the search term, e.g.: webinar, Webinar, WEBINAR, wEbInAr, etc..