1

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)

1

Please use the following

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

enter image description here

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


Functions used:

| improve this answer | |
  • This looks promising - how would it work with 25 exchanges to make? Would I keep nesting REGXREPLACE( x25? Not having much luck with that implementation... – Mr_P Nov 18 at 13:20
  • "Would I keep nesting REGXREPLACE...?" Yes. Or -as you already found out- by nesting SUBSTITUTE. "Thanks everyone for input". Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Nov 18 at 15:22
0

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

| improve this answer | |
  • Thanks for feedback - its not a one off, but will be needed on over 400 rows, each with one or more entries (!) So, looking for an iterative approach... – Mr_P Nov 18 at 10:43
  • added EDIT to question to provide more info. Again, thanks – Mr_P Nov 18 at 10:49
  • I think @Bijan Riazi-Farzad was referring to your need to do this a single time, or again and again in the future. Find and replace will have no trouble with 400 cells. – Phil Anderson Nov 18 at 12:45
  • understood @Phil Anderson, thanks for clarifying. It was indeed an automation I was looking for... – Mr_P Nov 19 at 12:47
0

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

| improve this answer | |
  • Again thanks for this workaround, but I'm looking at having one formula that I can iterate for each row. Also, the number of items varies too... – Mr_P Nov 18 at 10:44
  • added EDIT to question to provide more info. Again, thanks – Mr_P Nov 18 at 10:50
  • Thanks for further update - however =left(D4,find("ABC1",D4)-1)&"avocado,apricot,carrot"&right(D4,find("ABC1",D4)+4) results in avocado,apricot,carrotABC1... – Mr_P Nov 18 at 11:29
0

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 👍

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.