+ Reply to Thread
Results 1 to 4 of 4

Formula to replace all special characters with a dash

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    England
    MS-Off Ver
    Excel 365
    Posts
    34

    Formula to replace all special characters with a dash

    Hi,

    I would like help to create a formula that can take a string of text like this:

    Crafter's Companion 5in x 7in Die-Cut Card Bases & Envelopes Rectangular Aperture | Set of 20

    and converts it to this:

    crafter-s-companion-5in-x-7in-die-cut-card-bases-envelopes-rectangular-aperture-set-of-20

    It's for a site URL so needs to be free of special characters and spaces and instead have dashes. Note that eg "(space)|(space)" converts to one dash "-" rather than three dashes "---"

    I've tried SUBSTITUTE but I can only specify one character at a time.

    Would prefer a formula to a VBA script as it's for non-excel-savvy users.

    Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Formula to replace all special characters with a dash

    It will be multiple nested SUBSTITUTEs to convert the special characters to a space, combined with TRIM to remove multiple spaces, and a final SUBSTITUTE to replace the single spaces with a dash ( - ).
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-12-2011
    Location
    England
    MS-Off Ver
    Excel 365
    Posts
    34

    Re: Formula to replace all special characters with a dash

    I tried to nest but couldn't get it to work. Can you give an example of how the nested formula would look?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula to replace all special characters with a dash

    I used the following string of special characters: "|',./" (5 characters) For every extra special character you add, you'll need an additional SUBSTITUTE
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by ChemistB; 01-26-2021 at 01:11 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find & Replace all special characters with regular
    By mike_vr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2017, 04:23 AM
  2. find and replace special characters
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2015, 09:30 PM
  3. [SOLVED] Replace special characters with Space
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2014, 11:38 AM
  4. [SOLVED] Substitute formula - i want to replace cell containing special characters "??????*"
    By karthikskengeri in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2013, 06:21 AM
  5. Macro to replace European characters with non "special" English characters?
    By johanna0507 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2012, 09:13 AM
  6. search and replace special characters
    By jmhultin in forum Excel General
    Replies: 2
    Last Post: 10-14-2009, 12:07 PM
  7. [SOLVED] How do I find replace special characters?
    By zzapper in forum Excel General
    Replies: 1
    Last Post: 06-27-2005, 02:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1