CODEWITHSUNDEEP
excelvba
S K
S K

Reputation: 96

Remove special characters in excel vba

I am using this function to remove all supposed special character but still few special characters like "Ø" or "é" and all similar characters are still there How to handle this also in below given function.

What is in my mind is that to also create a string of those special characters like MyStr and to remove them.

Please suggest.

 Function ReplaceSplChars(TempStr As String) As String

Dim counter As Long, Position As Long
Dim MyStr As String, SplStr As String


MyStr = " 1234567890abcdefghijklmnopqrstuvwxyz.@"
Position = 1

For counter = 1 To Len(TempStr)
    SplStr = Mid(LCase(TempStr), Position, 1)

    If Not InStr(MyStr, SplStr) > 0 Then

        If SplStr = "-" Or SplStr = "_" Or SplStr = "'" Or SplStr = "/" Or SplStr = "," Then
            TempStr = Replace(TempStr, SplStr, " ", , 1)
            SplCharCount = SplCharCount + 1
            Position = Position + 1
        Else
            TempStr = Replace(TempStr, SplStr, "", , 1)
            SplCharCount = SplCharCount + 1
        End If
    Else
       Position = Position + 1
    End If

Next counter
ReplaceSplChars = TempStr
 End Function

Upvotes: 2

Views: 4523

Answers (1)

brettdj
brettdj

Reputation: 55672

A takes the hard work out of it.

test the code 

Sub EddieBetts()
MsgBox ReplaceSplChars(" 12345Ø67890abcdefghijklmnopqrstéuvwxyz.@")
End Sub

Pattern below excludes anything that is not

  • alphanumeric (\w)
  • space (\s)
  • @

code

 Function ReplaceSplChars(strIn As String) As String
 Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Pattern = "[^\w\s@]+"
        .Global = True
    ReplaceSplChars = Application.Trim(.Replace(strIn, vbNullString))
    End With
 End Function

Upvotes: 2

Related Questions