Ignore Non-Alphanumerics When Comparing Text

[Guest]

I am comparing the values in two cells using a macro. The data I'm comparing
is coming from two different sources. These sources may have slightly
different values in the corresponding cells, particularly the inclusion or
absence of non-alpha characters.

For instance, one cell might have "ABC, Inc." while another has "ABC, Inc"
(no period after Inc). The period is not material for my purposes, so I'd
like the values to still pass the compare test as equal. Similarly, I'd like
to ignore commas and hyphens for the comparison. Is there a way to do this?

 

[Harlan Grove]

KelleyS wrote...

I am comparing the values in two cells using a macro. The data I'm comparing
is coming from two different sources. These sources may have slightly
different values in the corresponding cells, particularly the inclusion or
absence of non-alpha characters.

For instance, one cell might have "ABC, Inc." while another has "ABC, Inc"
(no period after Inc). The period is not material for my purposes, so I'd
like the values to still pass the compare test as equal. Similarly, I'd like
to ignore commas and hyphens for the comparison. Is there a way to do this?

If you have Windows Script Host installed (and you do if you have
Internet Explorer 5.0 or higher installed), then you could use its
regular expression class to eliminate hyphens and replace all sequences
of nonalphanumeric characters with spaces. Then compare the resulting
strings. Something like


Sub cmp()
Dim n As Long, s1 As String, s2 As String
Dim re As Object

Set re = CreateObject("VBScript.RegExp")
re.Global = True

For n = 1 To 3
s1 = Range("A1:A3").Cells(n, 1).Value
s2 = Range("B1:B3").Cells(n, 1).Value

re.Pattern = "-"
s1 = re.Replace(s1, "")
s2 = re.Replace(s2, "")

re.Pattern = "[^A-Za-z0-9]+"
s1 = re.Replace(s1, " ")
s2 = re.Replace(s2, " ")

re.Pattern = " +"
s1 = re.Replace(s1, " ")
s2 = re.Replace(s2, " ")

Range("C1:C3").Cells(n, 1).Value = (Trim(s1) = Trim(s2))
Next n

Set re = Nothing
End Sub