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)),"_","-")
How can I combine multiple nested Substitute functions in Excel? - Stack Overflow
Are there any Excel alternatives that are actually BETTER than Excel?
excel - Substitute function not replacing text - Stack Overflow
Using substitute to remove multiple characters at the same time
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)),"_","-")
No need to use VBA anymore for this! Super simple formula now!
=REDUCE(A1,
2:
6,LAMBDA(a,b,SUBSTITUTE(a,b,OFFSET(b,0,1))))
Where A1 is the target cell to replace text b2:b6 is where the keywords are stored (texts to be replaced) c2:c6 (referenced by the offset) is where the replacing texts are stored
Credit: Chandoo
Obviously sheets and other free spreadsheeting software sucks, but are there any options that are better even if they are not free?
Sometimes it's because the cell or the column is locked. Or also it can be the Format cells number category.
To unlock: Right click on the cell or the column and in the "Format cells" window go to tab "Protection", uncheck "Locked" if checked.
To change format cells number category: Right click on the cell or the column and in the "Format cells" window go to tab "Number", change category to "General" especially if "Text" is already selected.
Return to your cell and re-apply the formula.
You may be able to bypass the issue with:
=REPLACE(A1,5,1,4)
(copied down to suit) where the second parameter is the 5th character in A1 (which just happens to be 5).
REPLACE.
(Does not work for first row in your example however.)
I want to use substitute with {".","!",";"} etc to remove the common non-alphanumeric characters. I know I can nest a bunch of substitute formulas together, but is there a way to do this by passing an array of symbols into the formula instead?
I'm not sure if you can use SUBSTITUTE with wildcards.
But you can use FIND to get the location of those words within the text, then LEFT and RIGHT to get the text outside of those words.
For example:
= LEFT(A1,FIND("replace",A1)-1)&"it is done"&RIGHT(A1,LEN(A1)-FIND("that",A1)-3)
Note FIND("replace",B4)-1 gets the location of the character right before "replace".
And LEN(B4)-FIND("that",B4)-3 gets the location of the character right after "that".
Both SUBSTITUTE and REPLACE functions will select a string within your text and update accordingly. However, the REPLACE function replaces the string based on its position.
=REPLACE(A1,SEARCH("replace",A1),SEARCH("that",A1)+ 4-SEARCH("replace",A1),"it is done")
You can use the SEARCH function to find the first word and then the length of the string that you want replaced.