Re: Extract letters and numbers from string

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
    ... the Like pattern tests can all be included into a single pattern test... ... Public Function AlphaNumeralsAs String ... I'm pulling the sender and subject info from Lotus Notes emails and ... so I have to lose the other characters in the extracted strings to fit ...
    (microsoft.public.excel.programming)
  • RE: Copying text to new cell
    ... Gary''s Student - gsnu200777 ... the last 6 right most characters of each string. ... I am working in Excel 2003 SP3 ...
    (microsoft.public.excel.worksheet.functions)
  • Re: String width not exceed column width
    ... I just did a test and 69 characters used 33 columns. ... Student said, a really difficult answer. ... Mike F ... I would like to know how many columns a given string will ...
    (microsoft.public.excel.programming)
  • Re: How to convert Infix notation to postfix notation
    ... If this is for an error message, why isn't it using stderr for its output? ... array of 15 characters, and you call this function with the limit 15 on ... Making sure that the only string I allocate and append to, ... because mulFactor in all versions must needs incorporate the functions ...
    (comp.lang.c)
  • 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)