How can I combine multiple nested Substitute functions in Excel?

Question

I am trying to set up a function to reformat a string that will later be concatenated. An example string would look like this:

Standard_H2_W1_Launch_123x456_S_40K_AB

Though sometimes the "S" doesn't exist, and sometimes the "40K" is "60K" or not there, and the "_AB" can also be "_CD" or _"EF". Finally, all underscores need to be changed to hyphens. The final product should look like this:

Standard-H2-W1-Launch-123x456-

I have four functions that if ran one after the other will take care of all of this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_")

=SUBSTITUTE(SUBSTITUTE(B2,"_40K",""),"_60K","")

=SUBSTITUTE(C2,"_S_","_")

=SUBSTITUTE(D2,"_","-")

I've tried a number of ways of combining these into one function, but I'm relatively new to this level of excel so I'm at a loss. Is there anyway to combine all of this so that it executes one command after the other in one cell?

Answer 1

To simply combine them you can place them all together like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"_AB","_"),"_CD","_"),"_EF","_"),"_40K",""),"_60K",""),"_S_","_"),"_","-")

(note that this may pass the older Excel limit of 7 nested statements. I'm testing in Excel 2010

---

Another way to do it is by utilizing Left and Right functions.

This assumes that the changing data on the end is always present and is 8 characters long

=SUBSTITUTE(LEFT(A2,LEN(A2)-8),"_","-")

This will achieve the same resulting string

---

If the string doesn't always end with 8 characters that you want to strip off you can search for the "_S" and get the current location. Try this:

=SUBSTITUTE(LEFT(A2,FIND("_S",A2,1)),"_","-")