Re: Extract letters and numbers from string

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" <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:EFE67991-C710-4009-A080-3BDC1152F1BF@xxxxxxxxxxxxxxxx
A great tip ! Thank you very much for taking the time to share.
--
Gary''s Student - gsnu200771


"Rick Rothstein (MVP - VB)" wrote:

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


"Gary''s Student" <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BDCD8F73-9BCE-4F95-9F06-AF7C3338F32A@xxxxxxxxxxxxxxxx
> 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
>
>
> "ward376" wrote:
>
>> 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
>>



.

Relevant Pages

  • Re: Extract letters and numbers from string
    ... While the Like operator patterns cannot begin to compare to those from a ... exclamation mark inside the square brackets says to look for characters NOT ... the Like pattern tests can all be included into a single pattern test... ... Public Function AlphaNumeralsAs String ...
    (microsoft.public.excel.programming)
  • Re: RegEx: How to ignore the number of whitespaces?
    ... matching character sequences in strings. ... what are the limitations of the "arbitrary Unicode characters?" ... Exactly one of them must be found in the string, ... Certain subsequences of a pattern may be marked as optional. ...
    (microsoft.public.dotnet.framework)
  • Re: RegExp irregularity in JScript
    ... of characters in the string is at ... All three strings match if the pattern is "."; ... the pattern as a submatch ") the entire string is returned, ... This looks like a bug in Microsoft's regular expression implementation (it ...
    (microsoft.public.scripting.jscript)
  • Re: RegEx: How to ignore the number of whitespaces?
    ... do not understand why the pattern "personal computer" will only match ... few special characters which denote very few pattern features (optional ... the alternative patterns must appear in the string. ...
    (microsoft.public.dotnet.framework)
  • Re: Prothon should not borrow Python strings!
    ... """It does not make sense to have a string without knowing what encoding ... same cul de sac as Python. ... Prothon_String_As_ASCII // raises error if there are high characters ... Python's split between byte strings and Unicode strings is ...
    (comp.lang.python)