Ignore punctuation

[Guest]

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.

 

[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 :