You are quite welcome.
While the Like operator patterns cannot begin to compare to those from a
Regular Expression parser, they are still quite flexible and you can still
get quite complex with them. Here, for example, is one I posted over in the
compiled VB newsgroups which had people scratching their heads in order to
fully see how it works when I first offered it to the group.
If Not Value Like "*[!0-9]*" Then
What the pattern does is insure the text in the variable Value is made up of
nothing but digits. It looks deceptively simple, but the double-negation
almost always causes people a problem when trying to decipher it. The
exclamation mark inside the square brackets says to look for characters NOT
in the range 0 through 9, the asterisks on either side says to look for this
non-digit anywhere within the text contained in the Value variable. So...
Value Like "*[!0-9]*"
will return True if any character in the text contained in Value is a
non-digit. The Not operator reverses that test meaning it is True only if no
non-digits exist anywhere in the text contained in Value; hence, it is a
test to insure that the text in Value is made up of only digits. You cannot
test for "digits only" directly (unless you loop through the text checking
each character one-by-one); it requires the double negation test to work.
Rick
Gary''s Student said:
A great tip ! Thank you very much for taking the time to share.
--
Gary''s Student - gsnu200771
Rick Rothstein (MVP - VB) said:
In this statement of yours....
If sChar Like "[0-9]" Or sChar Like "[a-z]" Or sChar Like "[A-Z]" Then
the Like pattern tests can all be included into a single pattern test...
If sChar Like "[0-9a-zA-Z]" Then
Rick
message
Here is one way. Try this UDF:
Public Function AlphaNumerals(rng As Range) As String
'
' gsnuxx
'
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Or sChar Like "[a-z]" Or sChar Like "[A-Z]"
Then
sStr1 = sStr1 & sChar
End If
Next
AlphaNumerals = sStr1
End Function
--
Gary''s Student - gsnu2007d
:
What's the best way to extract alpha and numeric characters from a
string?
I'm pulling the sender and subject info from Lotus Notes emails and
prefixing them to attachments' file names stripped from those emails,
so I have to lose the other characters in the extracted strings to fit
file-naming rules. I started to replace them, but Excel doesn't seem
to find some of the special characters - and there are so many it
seems it would be easier to identify alpha/numeric than illegal
characters.
Thanks!
Cliff Edwards
