Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Remove all special characters and spaces in one go

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Remove all special characters and spaces in one go

    Hi

    I am looking for a formula to remove special characters and spaces from a cell

    I have been using "substitute" but this requires me to know which character I want to remove and this isn't always known

    I have tried looking at some macro solutions but became lost quite quickly

    Any help would be much appreciated

    Thanks

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Do you have a list, or specification, of what constitutes a 'special character' for your circumstances?

    Edit: Alternatively a list, or specification, of what you want to keep?
    Last edited by Peter_SSs; Apr 1st, 2011 at 07:35 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Hi there,

    Try a UDF:

    Code:
    Function removeSpecial(sInput As String) As String
        Dim sSpecialChars As String
        Dim i As Long
        sSpecialChars = "\/:*?""<>|"
        For i = 1 To Len(sSpecialChars)
            sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
        Next
        removeSpecial = sInput
    End Function
    Alt+F11 - Insert - Module - Paste it in.

    You can add more special characters into the array if you wish.

    Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  4. #4
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,298
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Try Clean(A1)
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  5. #5
    Board Regular hardeep.kanwar's Avatar
    Join Date
    Aug 2008
    Posts
    679
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Try ASAP Utilities

    http://www.asap-utilities.com/asap-u...es-upgrade.php

    Its Very Fast and Good
    "PEOPLE LAUGH BECAUSE I AM DIFFERENT..AND I LAUGH BECAUSE THEY ARE ALL THE SAME.. THATS CALLED"ATTITUDE""-SWAMI VIVEKANANDA



    Hardeepkanwar

    Using Excel 2007

  6. #6
    New Member
    Join Date
    Mar 2011
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Hey
    Thanks for your replies.

    JamesW - Itried the UDF, and it seems to work great - thanks

    One further question if that is ok, now that I have stripped out the designated characters, I have been running a check down the column of data to find duplicates:
    =COUNTIF($AE$3:$AE$502,AE3)>1

    For some cells I am getting an error - could this happen if there are too many characters in a cell?

    Cheers

  7. #7
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    What is in cell AE3, and what error do you get?
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Quote Originally Posted by JamesW View Post
    Hi there,

    Try a UDF:

    Code:
    Function removeSpecial(sInput As String) As String
        Dim sSpecialChars As String
        Dim i As Long
        sSpecialChars = "\/:*?""<>|"
        For i = 1 To Len(sSpecialChars)
            sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
        Next
        removeSpecial = sInput
    End Function
    Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")
    Just wondering why you did most of the substitutions in the UDF but the space using a worksheet function? Why not
    - include the space in sSpecialChars = "\/:*?""<>| "
    - use a null string in sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
    - and just =removeSpecial(A1) in the worksheet?

    Probably could also use a Regular Expression UDF for this job too, though I'm not sure it would be any better/faster?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Very good point Peter, it was simply a case of I was in a rush and already had the UDF in my VBA reference folder.

    I thought about using Replace$(sInput, Mid$(sSpecialChars, i, 1), "") but then I thought if there was a space which is not produced by removing the special character then it would still be there: |abc 123\a| would result in "abc 123a". But you are right with the first point

    I simply overlooked it and did what I normally do: Overlook the blindingly obvious/simple approach
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,410
    Post Thanks / Like
    Mentioned
    77 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Remove all special characters and spaces in one go

    Quote Originally Posted by Peter_SSs View Post
    Probably could also use a Regular Expression UDF for this job too, though I'm not sure it would be any better/faster?
    So, having a go at this ..


    Function RemChrs(s As String) As String
        Static RegEx As Object
        
        If RegEx Is Nothing Then
            Set RegEx = CreateObject("VBScript.RegExp")
            With RegEx
                .Global = True
                .Pattern = "\\|/|:|\*|""|\?|<|>\|| "
            End With
        End If
        RemChrs = RegEx.Replace(s, "")
    End Function



    In worksheet use like

    =RemChrs(A1)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •