Can I Substitute multiple characters in one substitution?

nicolaig

New Member
Joined
Apr 17, 2014
Messages
2
I am using this:
=LOWER( SUBSTITUTE( A1, " ", "-"))
to replace spaces in a sentence with dashes and make it all lowercase. Occasionally there is a dash in the original cell so I end up with three dashes ---

Currently
One - Short Sentence
becomes
one---short-sentence
but I would like it to become
one-short-sentence

Currently I do another substitute to replace --- with - to another column but I would love to do it all in one formula and save myself a column. Is that possible?

 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
I am using this:
=LOWER( SUBSTITUTE( A1, " ", "-"))
to replace spaces in a sentence with dashes and make it all lowercase. Occasionally there is a dash in the original cell so I end up with three dashes ---

Currently
One - Short Sentence
becomes
one---short-sentence
but I would like it to become
one-short-sentence

Currently I do another substitute to replace --- with - to another column but I would love to do it all in one formula and save myself a column. Is that possible?
Just nest one SUBSTITUTE function call inside the other...

=LOWER(SUBSTITUTE(SUBSTITUTE(A1," ","-"),"--","-"))
 

nicolaig

New Member
Joined
Apr 17, 2014
Messages
2
Oh, so simple. Perfect.
Somehow I thought I would enter an endless loop. Thank you!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
Somehow I thought I would enter an endless loop.
A function returns a value (or array of values depending on the function)... that value (or array of values) can be used in a calculation or as an argument to any function just like any other value (or array)... a value (or array) is a value (or array), it doesn't matter how it comes into being.
 

sparkhill

New Member
Joined
Sep 16, 2017
Messages
1
It is an old thread, but I ran across it looking for something else. How about using
=LOWER(SUBSTITUTE(A1,{" ","--"},"-"))

It replaces " " or "--" with "-".
 

sqlvba

New Member
Joined
Sep 30, 2017
Messages
1
Yes. I am not sure if this is exactly what is needed. Use the following formula. =SUBSTITUTE(C1,A2,B2). C1 will contain the string of text. Column A will have the input value and Column B will have the output. Make sure that each value only appears once for both sides. I would create a list for all letters of the alphabet which are lower case and upper case. I would include a list for numbers as well. I would use symbols for the output values to avoid any mismatches. Make sure that this formula is next to the list of values. Drag the formula down. The next line will be =SUBSTITUTE(C2,A3,B3). To decode, put the substitute formula in reverse. Use the last line of the original formula. Use the following formulas, =SUBSTITUTE(C1,B2,A2). Hopefully, this makes sense and is helpful.
 

gavcol

New Member
Joined
Dec 22, 2016
Messages
9
[COLOR=#574123 said:
sparkhill[/COLOR]
Re: Can I Substitute multiple characters in one substitution?
It is an old thread, but I ran across it looking for something else. How about using
=LOWER(SUBSTITUTE(A1,{" ","--"},"-"))

It replaces " " or "--" with "-".​



SUBSTITUTE cannot replace different items in one go.
This is exactly what I'm looking for, a way to replace multiple values or special characters with a space or nothing ""

e.g. I have a value or url in a cell :
?suburb=316#?&rsf=syn:nameloc:nc:dt:spa

I need to remove characters like etc in a single formula.
Can this be done in a way other than nested SUBSTITUTE which I'm currently using and it's messy with 20+ different characters which I'd rather put in an array if possible
e.g. {"?=#:&/"} or {"?","&","=","|","#","%","/"}

I've seen options of using a lookup table to check for each character but there's 100k+ rows so I'm reluctant to use 20 lookups & replace/substitute for each character per row.

Aladin is right. Using Sparkhill's method only removes the first ? character and leaves the rest
=LOWER(SUBSTITUTE(DTWeb_62[@Variable],{"?","&","=","|","#","%","/"},""))


Is there a way to achieve this ?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,820
Office Version
2010
Platform
Windows
You can use a UDF (user defined function). Here is one for you to consider...
Code:
[table="width: 500"]
[tr]
	[td]Function ReplaceAll(Text As String, ReplaceWhat As Variant, ReplaceWith As String) As String
  Dim X As Long
  If Right(TypeName(ReplaceWhat), 2) <> "()" Then ReplaceWhat = Split(ReplaceWhat, "")
  For X = LBound(ReplaceWhat) To UBound(ReplaceWhat)
    Text = Replace(Text, ReplaceWhat(X), Chr(1))
  Next
  ReplaceAll = Replace(Text, Chr(1), ReplaceWith)
End Function
[/td]
[/tr]
[/table]
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ReplaceAll just like it was a built-in Excel function. For example,

=ReplaceAll(A1,{" ","--"},"-")

Simply make the comma delimited list inside the curly braces as long as you need.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,324
Messages
5,449,720
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top