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?

 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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," ","-"),"--","-"))
 
Upvote 0
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.
 
Upvote 0
It is an old thread, but I ran across it looking for something else. How about using
=LOWER(SUBSTITUTE(A1,{" ","--"},"-"))

It replaces " " or "--" with "-".
 
Upvote 0
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.
 
Upvote 0
[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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top