Help

Series of IF(Find()) statements to return list of found items

Topic Labels: Formulas
1808 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Sam_Eberle
5 - Automation Enthusiast
5 - Automation Enthusiast

I would like to create a formula to search 1 field and return the search results comma separated.
This formula only returns the last item found not all of them.

IF(FIND(“text1”,{field}),“text1”,"")&IF(FIND(“text2”,{field}),“text2”,"")

Ideally, this would return:
text1text2

3 Replies 3
Zollie
10 - Mercury
10 - Mercury

field (single line text)
text1
text2
text1text2

comma separated (formula)
text1,
text2,
text1,text2,

formula:

IF(
    FIND("text1",{field}),
    "text1" & ",",
    ""
)
&
IF(
    FIND("text2",{field}),
    "text2" & ",",
    ""
)

…and so on

remove trailing comma (formula)
text1
text2
text1,text2

formula:

IF(
    RIGHT({comma separated},1) = ",",
    LEFT({comma separated}, LEN({comma separated})-1),
    ""
)
Sam_Eberle
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much for this Zollie!
It still didn’t work but I discovered that it was because I was using FIND on a “Lookup” type field which only pulls the last item for some reason (Airtable, are you listening?).

It works perfect on a Link to Another Record type field which, for my purposes, is a good alternative.

Thanks for the Trailing Comma bonus :grinning_face_with_big_eyes:

When querying a Lookup field, Airtable will pass an array, but the FIND() function operates on strings, and Airtable doesn’t auto-convert between the two. You can force the conversion from array to string by concatenating the Lookup field with an empty string. That will turn all references to

{field}

into

{field} & "" 

On a side note, you can drop the empty strings at the end of the IF() functions, as Airtable will default to the proper BLANK() equivalent automatically if the condition returns FALSE.

With those two changes in place, your formula looks like this:

IF(FIND("text1",{field} & ""),"text1") & IF(FIND("text2",{field} & ""),"text2")