dcsimg
Results 1 to 6 of 6

Thread: need a code to remove special character

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    need a code to remove special character

    hi -

    I badly need your help to provide me a code to remove special character and replace it with the space. All I need to replace are column A, B, C, D. Also, need also help to convert the column E, G, H and Z to custom mm/dd/yyyy.

    Would appreciate a big help if you can help me on this.

    Thanks!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,560

    Re: need a code to remove special character

    Not sure what your special character is, but for example:

    Code:
    Sub replChar()
        Dim ws As Worksheet
        Dim rng As Range
        Dim specChar As String
        
        Set ws = ActiveSheet
        Set rng = ws.Range("a1:d1").EntireColumn
        
        specChar = "^"
        rng.Replace specChar, " "
        
    End Sub

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: need a code to remove special character

    Quote Originally Posted by vbfbryce View Post
    Not sure what your special character is, but for example:

    Code:
    Sub replChar()
        Dim ws As Worksheet
        Dim rng As Range
        Dim specChar As String
        
        Set ws = ActiveSheet
        Set rng = ws.Range("a1:d1").EntireColumn
        
        specChar = "^"
        rng.Replace specChar, " "
        
    End Sub
    ====

    thanks for the code however when i tried it the header also change it removes the special character...would you be able to help me on not to remove the special char on the header?
    Sub replChar()
    Dim ws As Worksheet
    Dim rng As Range
    Dim specChar As String

    Set ws = ActiveSheet
    Set rng = ws.Range("E5:I5").EntireColumn

    specChar = "_"
    rng.Replace specChar, " "
    specChar = "&"
    rng.Replace specChar, " "
    specChar = "("
    rng.Replace specChar, " "
    specChar = ")"
    rng.Replace specChar, " "
    specChar = "-"
    rng.Replace specChar, " "
    specChar = "_"
    rng.Replace specChar, " "
    specChar = "="
    rng.Replace specChar, " "
    specChar = "+"
    rng.Replace specChar, " "
    specChar = "\"
    rng.Replace specChar, " "
    specChar = "|"
    rng.Replace specChar, " "
    specChar = "]"
    rng.Replace specChar, " "
    specChar = "}"
    rng.Replace specChar, " "
    specChar = "{"
    rng.Replace specChar, " "
    specChar = "["
    rng.Replace specChar, " "
    specChar = "'"
    rng.Replace specChar, " "
    specChar = """"
    rng.Replace specChar, " "
    specChar = ";"
    rng.Replace specChar, " "
    specChar = ":"
    rng.Replace specChar, " "
    specChar = "/"
    rng.Replace specChar, " "
    specChar = ">"
    rng.Replace specChar, " "
    specChar = "."
    rng.Replace specChar, " "
    specChar = "<"
    rng.Replace specChar, " "
    specChar = ","
    rng.Replace specChar, " "

    Columns("B:B").Select
    Selection.NumberFormat = "0"
    Range("C:C,D,J:J").Select
    Range("J1").Activate
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,560

    Re: need a code to remove special character

    To bypass the header row (1):

    Code:
    Sub replChar()
        Dim ws As Worksheet
        Dim rng As Range
        Dim specChar As String
        Dim lastRow As Long
        
        Set ws = ActiveSheet
        lastRow = ws.Range("a" & Rows.Count).End(xlUp).Row  'last row in A with data
        'Set rng = ws.Range("a1:d1").EntireColumn
        Set rng = ws.Range("a2:d" & lastRow)
        
        specChar = "^"
        rng.Replace specChar, " "
    End Sub

  5. #5

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: need a code to remove special character

    Quote Originally Posted by vbfbryce View Post
    to bypass the header row (1):

    Code:
    sub replchar()
        dim ws as worksheet
        dim rng as range
        dim specchar as string
        dim lastrow as long
        
        set ws = activesheet
        lastrow = ws.range("a" & rows.count).end(xlup).row  'last row in a with data
        'set rng = ws.range("a1:d1").entirecolumn
        set rng = ws.range("a2:d" & lastrow)
        
        specchar = "^"
        rng.replace specchar, " "
    end sub
    it works, thanks so much!!!!!

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: need a code to remove special character

    Quote Originally Posted by marcuz_jozef View Post
    ====

    thanks for the code however when i tried it the header also change it removes the special character...would you be able to help me on not to remove the special char on the header?
    Sub replChar()
    Dim ws As Worksheet
    Dim rng As Range
    Dim specChar As String

    Set ws = ActiveSheet
    Set rng = ws.Range("E5:I5").EntireColumn

    specChar = "_"
    rng.Replace specChar, " "
    specChar = "&"
    rng.Replace specChar, " "
    specChar = "("
    rng.Replace specChar, " "
    specChar = ")"
    rng.Replace specChar, " "
    specChar = "-"
    rng.Replace specChar, " "
    specChar = "_"
    rng.Replace specChar, " "
    specChar = "="
    rng.Replace specChar, " "
    specChar = "+"
    rng.Replace specChar, " "
    specChar = "\"
    rng.Replace specChar, " "
    specChar = "|"
    rng.Replace specChar, " "
    specChar = "]"
    rng.Replace specChar, " "
    specChar = "}"
    rng.Replace specChar, " "
    specChar = "{"
    rng.Replace specChar, " "
    specChar = "["
    rng.Replace specChar, " "
    specChar = "'"
    rng.Replace specChar, " "
    specChar = """"
    rng.Replace specChar, " "
    specChar = ";"
    rng.Replace specChar, " "
    specChar = ":"
    rng.Replace specChar, " "
    specChar = "/"
    rng.Replace specChar, " "
    specChar = ">"
    rng.Replace specChar, " "
    specChar = "."
    rng.Replace specChar, " "
    specChar = "<"
    rng.Replace specChar, " "
    specChar = ","
    rng.Replace specChar, " "

    Columns("B:B").Select
    Selection.NumberFormat = "0"
    Range("C:C,D,J:J").Select
    Range("J1").Activate
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

    You do not need to write so many lines if you want to remove them. Here is a shorter way to do it

    Code:
    Sub replChar()
        Dim ws As Worksheet
        Dim rng As Range
        Dim SpecialCharacters As String, AR() As String
        Dim lastrow As Long, i As Long
        
        '~~> Add more to the list if you want. But separate them with a space
        SpecialCharacters = "_ & ( ) - _ = + \ / > < . | ] [ { } ' """" ; : , % ^"
        
        Set ws = ActiveSheet
        
        With ws
            lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
                      
            Set rng = .Range("E2:I" & lastrow)
            
            AR = Split(SpecialCharacters)
            
            For i = LBound(AR) To UBound(AR)
                rng.Replace AR(i), ""
            Next i
        End With
    End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width