I have a cell that contains mixed input of one or more delimited items eg:
apple,ABC1,banana,BBC2,FGC3
I need to replace the items that contain ALPHA code eg ABC1
(from a finite list of about 25 codes)
with a new sequence (eg ABC1
becomes avocado,apricot,carrot
, eg BBC2
becomes plum,damson,leek
)
so that the new output looks like this
apple,avocado,apricot,carrot,banana,plum,damson,leek...
etc
Can I do this with a formula?
I've tried switch
and if
but these only change the entire cell when a single item gets matched...
=SWITCH(Q2,
"ABC1", "avocado,apricot,carrot",
"BBC2", "plum,damson,leek"
)
EDIT - thanks for feedback already, more info will be helpful here... This cell is in one column of about 400 rows, and I'm looking at creating a formula that can iterate through all rows. Also the number of delimited items in each cell varies (eg the next row might look like this: ABC1,banana
)