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?
This question is related to
excel
excel-formula
nested
substitution
nested-function
The answer is
- nesting
SUBSTITUTE()in a string can be nasty, however, it's always possible to arrange it:
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
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.
=SUBSTITUTE(text, old_text, new_text)
if: a=!, b=@, c=#,... x=>, y=?, z=~, " "=" "
then: abcdefghijklmnopqrstuvwxyz ... try this out
equals: !@#$%^&*()-=+[]\{}|;:/<>?~ ... ;}? ;*(| ]:;
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","&"),"h","*"),"i","("),"j",")"),"k","-"),"l","="),"m","+"),"n","["),"o","]"),"p","\"),"q","{"),"r","}"),"s","|"),"t",";"),"u",":"),"v","/"),"w","<"),"x",">"),"y","?"),"z","~")," "," ")
Source: Stackoverflow.com