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

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