Remove unicode characters from string on excel sheet

Question

I need some directions on how to use regex to remove special characters such as fractions,exponents,degree symbol and any other non normal letters in a string. I know the code below find the string base on those criteria but does it include all unicode characters?

Code for your attention:

Dim strPattern As String: strPattern = "[^\u0000-\u007F]"
Dim regEx As Object

Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = strPattern

For Each cell In ActiveSheet.Range("C:C") ' Define your own range here
    If strPattern <> "" Then              ' If the cell is not empty
        If regEx.Test(cell.Value) Then    ' Check if there is a match
            cell.Interior.ColorIndex = 6  ' If yes, change the background color
        End If
    End If
Next

What makes you certain that your cells don't have hidden special characters that you don't realize are there, but which the code is detecting? — Marc
– Marc, Commented Jan 18, 2018 at 15:26
because i can see the text in that cell and it does not have special characters. — pokemon_Man
– pokemon_Man, Commented Jan 18, 2018 at 15:27
Hence my use of the word "hidden." Completely delete the contents of one of the cells in your range and type a clean string into it. See if it gets highlighted. If it doesn't, then you'll know there are characters you're not seeing in the other ones. — Marc
– Marc, Commented Jan 18, 2018 at 15:28
I have copied and pasted your code into a new macro and run it. I cannot reproduce the issue. It works correctly for me. — Greg Viers
– Greg Viers, Commented Jan 18, 2018 at 15:34

Gary's Student · Accepted Answer · 2018-01-18 16:25:05Z

1

This does not use regular expressions.

There are many potentially "bad" characters. Rather than trying to remove them, just keep the "good" ones.

Select some cell and run this short macro:

Sub UniKiller()
    Dim s As String, temp As String, i As Long
    Dim C As String

    s = ActiveCell.Value
    If s = "" Then Exit Sub
    temp = ""

    For i = 1 To Len(s)
        C = Mid(s, i, 1)
        If AscW(C) > 31 And AscW(C) < 127 Then
            temp = temp & C
        End If
    Next i
    ActiveCell.Value = temp
End Sub

If you need to "clean" more than one cell, put the logic in a loop.

answered Jan 18, 2018 at 16:25

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove unicode characters from string on excel sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related