Ignore punctuation

[Purnima]

Hi,
Is there a way to ignore all punctuation and special characters when
comparing two string expressions?

[Tom Ogilvy]

I can't think of anything that does that. The worksheet function clean can
remove non-printing characters. I think you would need to write a UDF to do
it.

--
Regards,
Tom Ogilvy

"Purnima" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> Is there a way to ignore all punctuation and special characters when
> comparing two string expressions?

[keepITcool]

first strip the strings of "unwanted" characers..
before you compare..

Option Compare Binary

Function StripString(s$)
'Note: LIKE depends on option compare.see VBA help.
Dim i&, r$, c$
Const mask = "[A-Za-z0-9]"
For i = 1 To Len(s)
c = Mid$(s, i, 1)
If c Like mask Then r = r & c
Next
StripString = r
End Function

then compare like..

If StrComp(StripString(MyString1), _
StripString(myString2)) = 0 Then
MsgBox "stripped compare OK"
ElseIf StrComp(StripString(MyString1), _
StripString(myString2), vbTextCompare) = 0 Then
MsgBox "stripped compare CaseInsensitive OK"
Else
MsgBox "NOT"
End If







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Purnima wrote :

> Hi,
> Is there a way to ignore all punctuation and special characters when
> comparing two string expressions?

[Leith Ross]

Hello Purnima,

Here is a macro to do what you want. It compares 2 strings, ignoring any character that is not "0 -9, A-Z, a-z". It returns True if the strings test equal, false if not.
_________________________________________________________________

Example:

Result = CompareAlphaNumerics("This String", "T@his Str<>:ing")

Result is "True"
_________________________________________________________________

Macro Code:

Public Function CompareAlphaNumerics(ByVal String1 As String, ByVal String2 As String) As Boolean

'Compare Only Numbers(0-9), Upper Case Letters(A -Z),
'and Lower Case Letters(a -z). Ignore All Others

Dim I As Long
Dim TmpStr1 As String
Dim TmpStr2 As String

For I = 1 To Len(String1)
TestChr = Asc(Mid(String1, I, 1))
Select Case TestChr
Case 48 To 57, 65 To 90, 97 To 122
TmpStr1 = TmpStr1 & Chr(TestChr)
End Select
Next I

For I = 1 To Len(String2)
TestChr = Asc(Mid(String2, I, 1))
Select Case TestChr
Case 48 To 57, 65 To 90, 97 To 122
TmpStr2 = TmpStr2 & Chr(TestChr)
End Select
Next I

If TmpStr1 = TmpStr2 Then CompareAlphaNumerics = True

End Function
_________________________________________________________________

Hope this helps,
Leith Ross