Remove Special Characters

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


Sub cleanAllText()
   Dim rngUsed As Range, rngCheck As Range
   Dim i As Long
'Change Column as per requirement like ("K:K")
   Set rngUsed = Range("J:J") 
   'Set rngUsed = Range(rngUsed, _
      ' rngUsed.SpecialCells(xlLastCell))
   Application.Calculation = xlCalculationManual
   For Each rngCheck In rngUsed.Cells
       If rngCheck.Formula <> "" Then
         If Left(rngCheck, 1) <> "=" Then
         rngCheck = removeSpecial(rngCheck.Value)
         End If
       End If
   Next rngCheck
End Sub

Popular posts from this blog

Comments