Extract letters and numbers from string

[ward376]

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

 

[Gary''s Student]

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

 

[Reg]

How about creating one string of all the characters you want to keep then
build a 'legal' string by ignoring any others (the following is just a quick
example and wouild need tidying up before real world use):

ValidCHARS = "012345657890abce...."
LegalString = ""

For i = 1 to len(testString)
c = mid(testring,i,1)
If instr(c,Validchars,1) >0 then
legalString=LegalString + c
Endif
Next i

Reg

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Gary''s Student]

A great tip ! Thank you very much for taking the time to share.
--
Gary''s Student - gsnu200771

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

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

 

[Rick Rothstein \(MVP - VB\)]

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

A great tip ! Thank you very much for taking the time to share.
--
Gary''s Student - gsnu200771

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

 

[ward376]

Everyone - Thanks for the lines and the insight!

Cliff Edwards

 

[Reg]

gobsmacked, thats is way cool

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

A great tip ! Thank you very much for taking the time to share.
--
Gary''s Student - gsnu200771

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