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
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
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
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 
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
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'
)
), ', $', '')
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'
)
), ', $', '')
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.
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.
@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?
@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?
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.