Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Formulas
1000 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Marijke_Cortneb
4 - Data Explorer
4 - Data Explorer

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?

1 Reply 1

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.