How do I remove all special characters which don't fall under ASCII category in VBA?
These are some of the symbols which appear in my string.
Œ œ Š š Ÿ ƒ
There are many more such characters.
These don't belong to ASCII category as you can see here http://www.ascii.cl/htmlcodes.htm
I tried something like this
strName = Replace(strName, ChrW(376), " ")
Would a RegEx solution be of interest to you?
There are plenty of examples for different languages on this site - here's a C# one: How can you strip non-ASCII characters from a string? (in C#).
Try this for VBA:
Private Function GetStrippedText(txt As String) As String
Dim regEx As Object
Set regEx = CreateObject("vbscript.regexp")
regEx.Pattern = "[^\u0000-\u007F]"
GetStrippedText = regEx.Replace(txt, "")
End Function
.Replace function inside your loop. That might help with speed.Don't need to loop each character
Maybe late, but maybe it helps someone:
Public Function StripNonAsciiChars(ByVal InputString As String) As String
Dim i As Integer
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.MultiLine = True
.IgnoreCase = True
.Pattern = "[^\u0000-\u007F]"
StripNonAsciiChars = Application.WorksheetFunction.Trim(RegEx.Replace(InputString, " "))
End With
End Function
Replace function too, e.g. you may put dashes, or leave the spaces, this is up to your needs. Lately, starting with the ClickToRun aproatch of MS Office gives 1004 error for the base excel functions in VBA...Try with below
Function ClearUnwantedString(fulltext As String) As String
Dim output As String
Dim character As String
For i = 1 To Len(fulltext)
character = Mid(fulltext, i, 1)
If (character >= "a" And character <= "z") Or (character >= "0" And character <= "9") Or (character >= "A" And character <= "Z") Then
output = output & character
End If
Next
ClearUnwantedString = output
End Function
Sub test()
a = ClearUnwantedString("dfjŒœŠdskl")
End Sub
What do you get when you write the following in the immediate window?
?Replace("ŸŸŸŸ", ChrW(376), "ale")
I get: alealealeale
try application.clean()
it will remove all unprintable characters
.clean exist under worksheetfunction but it can also be called under application. I'll just leave the answer here for reference.Supposing you have:
Then the following code will get the String from A1 and let through only ANSI (code 0 to 255) in A2.
Sub test()
Dim s1 As String, s2 As String, c As String, i As Long, iAsc As Integer
s1 = Range("A1").Value
s2 = ""
For i = 1 To Len(s1)
c = Mid(s1, i, 1)
iAsc = AscW(c)
If iAsc <= 255 Then
s2 = s2 & c
End If
Next
Range("A2").Value = s2
End Sub
Longthat identifies a character, but doesn't allow values greater than65535(hex value&HFFFF). It might be of some practical interest to find systematic ways to overcome this restriction - c.f. six solutions at Get UniCode characters with CharCode greater hex FFFF, e.g. in Unicode block1F300-1F5FF"Miscellaneous symbols and pictographs" :-)