How can I combine multiple nested Substitute functions in Excel?

Thanks for the idea of breaking down a formula Werner!

Using Alt+Enter allows one to put each bit of a complex substitute formula on separate lines: they become easier to follow and automatically line themselves up when Enter is pressed.

Just make sure you have enough end statements to match the number of substitute( lines either side of the cell reference.

As in this example:

=
substitute(
substitute(
substitute(
substitute(
B11
,"(","")
,")","")
,"[","")
,"]","")

becomes:

=
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

which works fine as is, but one can always delete the extra paragraphs manually:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B12,"(",""),")",""),"[",""),"]","")

Name > substitute()

[American Samoa] > American Samoa

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