Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 24, 2022 12:14 AM
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
Sep 24, 2022 09:51 AM
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
Sep 24, 2022 10:23 AM
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:
Sep 24, 2022 04:10 PM
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'
)
), ', $', '')
Sep 24, 2022 06:16 PM
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.
Sep 24, 2022 06:19 PM
@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?
Sep 24, 2022 09:12 PM
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.