If cell contains 'search', 'google' 'bing' (etc) then return 'search engine'

Hey All,

I’m struggling to build a formula in airtable for the following. I have a list of free text data in my column Source, where customers have put in on where they have found us, ranging from ‘Search engine’ to ‘Google’, 'Bing, ‘My friend James’, ‘A friend’, etc… I want to create a column that groups several of these free text entries together, so for example for ‘Search engine’ and ‘Google’ we return ‘search engine’ for all entries that contain the text ‘search’ or ‘google’ in column Marketing channel.
(I know I won’t be able to capture all, but I can continue to build on this formula to include all)

image

I tried a Switch formula, but that requires the text to be exact, and not ‘contains’. I tried an If Find formula, but i’m struggling to get the format right and I get a lot of errors.

Could anyone help?

If you want to use the FIND function, the format would be something like:

IF(
  OR(
    FIND(LOWER(Source), "google"),
    FIND(LOWER(Source), "bing"),
    FIND(LOWER(Source), "search")
  ),
  "Search Engine",
  "Other"
)

I added LOWER so that capitalization won’t matter.

Or you could do this with REGEX:

IF(
  REGEX_MATCH(LOWER(Source), "(.*)(google|bing|search)(.*)"),
  "Search Engine",
  "Other"
)

^ this is cleaner and perhaps a bit more reliable.

4 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.