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

User · Answer

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

User · Answer

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        -

User · Answer

SUBSTITUTE text  old text  new text   if  a    b    c       x  gt   y    z                then  abcdefghijklmnopqrstuvwxyz     try this out equals         amp    -            lt  gt                                       RULES    1  text to substitute is in cell A1  2  max 64 substitution levels  the formula below only has 27 levels  alphabet   space    2   old text  cannot also be a  new text   ie  if a z    z cannot be  old text    ---so if a z b y    y b z a  then the result is  ---abcdefghijklmnopqrstuvwxyz   zyxwvutsrqponnopqrstuvwxyz  and z changes to a then changes back to z       pattern starts to fail after m n  n m    and n becomes n    The formula is    SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE SUBSTITUTE A1  a        b        c        d        e        f        g    amp     h        i        j        k   -    l        m        n        o        p        q        r        s        t        u        v        w    lt     x    gt     y        z

User · Answer

nesting SUBSTITUTE   in a string can be nasty  however  it s always possible to arrange it

User · Answer

I would use the following approach    SUBSTITUTE LEFT A2 LEN A2 -X       -     where X denotes the length of things you re not after  And  for X I d use   ISERROR FIND   S  A2 1   2    ISERROR FIND   40K  A2 1   4    ISERROR FIND   60K  A2 1   4    ISERROR FIND   AB  A2 1   3    ISERROR FIND   CD  A2 1   3    ISERROR FIND   EF  A2 1   3    The above ISERROR FIND  X        x will return 0 if X is not found and x  the length of X  if it is found  So technically you re trimming A2 from the right with possible matches   The advantage of this approach above the other mentioned is that it s more apparent what substitution  or removal  is taking place  since the  substitution  is not nested

[excel] How can I combine multiple nested Substitute functions in Excel?

Examples related to excel

Examples related to excel-formula

Examples related to nested

Examples related to substitution

Examples related to nested-function