Search a text field against multiple strings

[Subir_Kumedan]

Hi everyone,

I did some searching and did not find an answer.

I am searching a text field to see if it contains one of a set of words

eg. Search for
design, designer, product designer, experience designer, UX
if found - set result to “Design”

I have similar sets of words I want to search for and mark if a result is found

How do I go about doing that?

Cheers,
Subir

[Subir_Kumedan]

So it’s almost the opposite use case of this one ( I think)

Searching for a string value within an column with array of strings
(Searching for a string value within an column with array of strings)

I’m searching using an array of search strings within a text column

HTH

[Subir_Kumedan]

OK Progress

What Works

IF(OR(
SEARCH(‘Design’,Headline),
SEARCH(‘Designer’, Headline),
SEARCH(‘Creative’,Headline),
SEARCH(‘design’,Headline),
SEARCH(‘designer’,Headline)),
‘Design’, BLANK())

What DOESN’T Work

IF(OR(
SEARCH(‘Design’,Headline),
SEARCH(‘Designer’, Headline),
SEARCH(‘Creative’,Headline),
SEARCH(‘design’,Headline),
SEARCH(‘designer’,Headline)),
‘Design’, BLANK(),
IF(OR(
SEARCH(‘UX’,Headline),
SEARCH(‘UI’, Headline),
SEARCH(‘Experience’,Headline),
SEARCH(‘experience’,Headline),
SEARCH(‘ux’,Headline)),
‘UX’, BLANK()
)
)

Airtable doesn’t like the second formula and simply strips out the second IF(OR) stateement.

Help please. Open to using another method to achieve the same result :slightly_smiling_face:

[kuovonne]

Subir_Kumedan:

Airtable doesn’t like the second formula and simply strips out the second IF(OR) stateement.

That is because the IF function takes a maximum of three parameters. The way you have it written, there are four parameters. Take out your first BLANK().

IF(
  OR(
    SEARCH('Design',Headline),
    SEARCH('Designer', Headline),
    SEARCH('Creative',Headline),
    SEARCH('design',Headline),
    SEARCH('designer',Headline)
  ),
  'Design',
  IF(
    OR(
      SEARCH('UX',Headline),
      SEARCH('UI', Headline),
      SEARCH('Experience',Headline),
      SEARCH('experience',Headline),
      SEARCH('ux',Headline)),
      'UX'
  )
)

Here is another possible version that converts the {Headline} to lower case so you don’t have to search for so many different options.

IF(
  OR(
    SEARCH('design', LOWER({Headline})),
    SEARCH('creative', LOWER({Headline}))
  ),
  'Design',
IF(
  OR(
    SEARCH('ux', LOWER({Headline})),
    SEARCH('ui', LOWER({Headline})),
    SEARCH('experience', LOWER({Headline}))
  ),
  'UX'
))

On the other hand, if you want to return multiple keywords, you many need a different formula:

TRIM(CONCATENATE(
  IF(
    OR(
      SEARCH('design', LOWER({Headline})),
      SEARCH('creative', LOWER({Headline}))
    ),
    'Design '
  ),
  IF(
    OR(
      SEARCH('ux', LOWER({Headline})),
      SEARCH('ui', LOWER({Headline})),
      SEARCH('experience', LOWER({Headline}))
    ),
    'UX'
  )
))

If you want to get into REGEX instead of seach:

REGEX_REPLACE(CONCATENATE(
  IF(
    REGEX_MATCH(
      LOWER({Headline}),
      'design|creative'
    ),
    'Design, '
  ),
  IF(
    REGEX_MATCH(
      LOWER({Headline}),
      'ux|ui|experience'
    ),
    'UX'
  )
), ', $', '')

[Subir_Kumedan]

Thanks @kuovonne You rock.

These are so awesome. I’ll try them out and let you know if I run into any further issues. I especially like the regex one since that seems nice and compact.

[Subir_Kumedan]

@kuovonne One quick follow-up - if I wanted to search in two Cells - Headline, Description - with an OR option?

Aka, the result is found in either of those 2 fields - is that possible as well?

[Subir_Kumedan]

The regex works with 2 of the IF statements, but if I add a third or fourth IF statement, the formula generates an ‘invalid’ error

REGEX_REPLACE(CONCATENATE(
IF(
REGEX_MATCH(
LOWER({Headline}),
‘design|creative’
),
'Design, ’
),
IF(
REGEX_MATCH(
LOWER({Headline}),
‘ux|ui|experience|uiux’
),
'UX, ’
)
IF(
REGEX_MATCH(
LOWER({Headline}),
‘operations|analyst|systems’
),
‘Ops’
)
), ‘, $’, ‘’)

Does that have something to do with the last line or is it something else?

Thanks again for the help.