Can I Substitute multiple characters in one substitution?
Results 1 to 10 of 10

Thread: Can I Substitute multiple characters in one substitution?

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can I Substitute multiple characters in one substitution?

    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?


  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,974
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Quote Originally Posted by nicolaig View Post
    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," ","-"),"--","-"))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Oh, so simple. Perfect.
    Somehow I thought I would enter an endless loop. Thank you!

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,974
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Quote Originally Posted by nicolaig View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Sep 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 "-".

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Quote Originally Posted by sparkhill View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Sep 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    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.

  8. #8
    New Member
    Join Date
    Dec 2016
    Location
    Sydney Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Quote Originally Posted by
    [COLOR=#574123
    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 "-".


    Quote Originally Posted by Aladin Akyurek View Post
    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 by gavcol; Mar 11th, 2019 at 09:26 AM.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,974
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    You can use a UDF (user defined function). Here is one for you to consider...
    Code:
    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
    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 by Rick Rothstein; Mar 11th, 2019 at 03:15 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Dec 2016
    Location
    Sydney Australia
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I Substitute multiple characters in one substitution?

    Thanks Rick,
    Much appreciated.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •