How to ignore special characters when referencing a string in excel Vba

Question

I am trying to use vba to read client feedback and reference it to a set of keywords mapped to categories. However the problem I am having is that on occasion, clients use special characters such as “ -^<* ‘ in their comments and this is breaking my code as soon as it hit such a string. How can I make my code ignore these special characters and keep moving down the rows to search for criteria? Thanks in advance

Use an encoder as used in a url encoding can solve your problem. w3schools.com/tags/ref_urlencode.asp — Ruchan
– Ruchan, Commented Mar 18, 2019 at 13:10
How do you "search for criteria"? Do you compare the value of each cell individually by string comparison? — Asger
– Asger, Commented Mar 18, 2019 at 13:15
It is easy to do if you update your question with a specific list of the characters you want to ignore. — Gary's Student
– Gary's Student, Commented Mar 18, 2019 at 13:17
This is one scenario where using RegEx seems to be fit, so you can filter only the characters you want, rather than trying to remove each possible new code breaking char. replace-text-using-regex-in-excel-vba — FAB
– FAB, Commented Mar 18, 2019 at 13:17

Gary's Student · Accepted Answer · 2019-03-18 15:00:26Z

0

First place the data to be "cleaned-up" in column A, then run:

Sub Kleanup()
    Dim A As Range, aa As Range, L As Long, i As Long
    Dim CH As String, temp As String
    Set A = Range("A:A")
    For Each aa In Intersect(A, ActiveSheet.UsedRange)
        If aa <> "" Then
            L = Len(aa)
            temp = ""
            For i = 1 To L
                CH = Mid(aa, i, 1)
                If CH Like "[A-Za-z0-9]" Then
                    temp = temp & CH
                End If
            Next i
            aa.Value = temp
        End If
    Next aa
End Sub

It will remove all characters except 0 through 9 and upper case letters and lower case letters.

edited Mar 18, 2019 at 15:00

answered Mar 18, 2019 at 14:22

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

How to ignore special characters when referencing a string in excel Vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related