Editing comma delimited cell in Google Sheets to replace some items

Question

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)

Answer 1

Please use the following

=ARRAYFORMULA(REGEXREPLACE(
                 REGEXREPLACE(N1:N3,"(ABC1)","xxx,XXX,xx"), 
                                    "(BBC2)","z,zz,xyz"))

(you should to adjust ranges and values to your needs)

---

Functions used:

• ArrayFormula
• REGEXREPLACE

Answer 2

If this is a one-off:

Select the cells you want to change (don't select if you want this to be done for all cells), then press Ctrl+H (Search and replace), then for the example that you have given:

Find: ABC1

Replace: avocado,apricot,carrot

Answer 3

If you need to do this consistently, then here is a workaround:

Let's assume that [apple,ABC1,banana,BBC2,FGC3] is in cell D4

E5: [=left(D4,find("ABC1",D4)-1)]

F5: [avocado,apricot,carrot]

G5: [=right(D5,(len(D5)-len(E5)-4))]

H5: [=E5&F5&G5]

Once you see the logic behind this, you can combine the above into one formula in one cell and it will look like this:

[=left(D4,find("ABC1",D4)-1)&"avocado,apricot,carrot"&right(D4,find("ABC1",D4)+4)]

Where the "4" at the end of the formula is the length of the string that you are replacing (in this case "ABC1")

Answer 4

Solved this with nested SUBSTITUTE formula:

=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            Q2, "ABC1", "avocado,apricot,carrot"),
                "BBC2", "plum,damson,leek"),
                "XYZ7", "cherry,fig,date")

Just kept iterating on nesting x 25.

Thanks everyone for input 👍