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

[Sam_Eberle]

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

[Zollie]

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]

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:

[Justin_Barrett]

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")