I'm trying to test a string for alpha chars, if alpha char append char to a
new string
The following does not work. Is there a tweak I can apply?

l_Alpha_chars = "abcd1"
For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr = [a..z] Then
NewStr = NewStr & l_chr
End If
Next q

On Fri, 18 Sep 2009 15:53:01 -0700, miek
wrote:

I'm trying to test a string for alpha chars, if alpha char append char to a
new string
The following does not work. Is there a tweak I can apply?

l_Alpha_chars = "abcd1"
For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr = [a..z] Then
NewStr = NewStr & l_chr
End If
Next q

For what you seem to be doing, using your technique:

===========================
Option Explicit
Sub bar()
Const l_Alpha_chars As String = "abcd1"
Dim l_chr As String
Dim NewStr As String
Dim q As Long

For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr Like "[a-z]" Then
NewStr = NewStr & l_chr
End If
Next q

Debug.Print NewStr

End Sub
==========================
--ron

Thank I had a syntax error

Was using: If l_chr = [a..z] Then
Now using: If l_chr Like "[a-z]" Then

Thanks again

"Ron Rosenfeld" wrote:

For what you seem to be doing, using your technique:

===========================
Option Explicit
Sub bar()
Const l_Alpha_chars As String = "abcd1"
Dim l_chr As String
Dim NewStr As String
Dim q As Long

For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr Like "[a-z]" Then
NewStr = NewStr & l_chr
End If
Next q

Debug.Print NewStr

End Sub
==========================
--ron

On Sat, 19 Sep 2009 09:28:01 -0700, miek
wrote:

Thank I had a syntax error

Was using: If l_chr = [a..z] Then
Now using: If l_chr Like "[a-z]" Then

Thanks again


You're welcome.
--ron

Try some code like the following:

Function IsCharAlpha(C As String) As Boolean
IsCharAlpha = UCase(Left(C, 1)) Like "[A-Z]"
End Function

Function IsStringAlpha(s As String) As Boolean
IsStringAlpha = _
(UCase(s) Like Application.WorksheetFunction.Rept("[A-Z]",
Len(s))) _
And _
Len(s) 0
End Function


The IsCharAlpha function tests if a single character C is alpha. The
IsStringAlpha functions tests if a string of characters S is alpha.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 18 Sep 2009 15:53:01 -0700, miek
wrote:

I'm trying to test a string for alpha chars, if alpha char append char to a
new string
The following does not work. Is there a tweak I can apply?

l_Alpha_chars = "abcd1"
For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr = [a..z] Then
NewStr = NewStr & l_chr
End If
Next q

Function IsStringAlpha(s As String) As Boolean
IsStringAlpha = (UCase(s) Like Application.WorksheetFunction. _
Rept("[A-Z]", Len(s))) And Len(s) 0
End Function

This somewhat shorter function will do exactly what your function does...

Function IsStringAlpha(S As String) As Boolean
IsStringAlpha = Not S like "*[!A-Za-z]*" And Len(S) 0
End Function

--
Rick (MVP - Excel)


"Chip Pearson" wrote in message
...

Try some code like the following:

Function IsCharAlpha(C As String) As Boolean
IsCharAlpha = UCase(Left(C, 1)) Like "[A-Z]"
End Function

Function IsStringAlpha(s As String) As Boolean
IsStringAlpha = _
(UCase(s) Like Application.WorksheetFunction.Rept("[A-Z]",
Len(s))) _
And _
Len(s) 0
End Function


The IsCharAlpha function tests if a single character C is alpha. The
IsStringAlpha functions tests if a string of characters S is alpha.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 18 Sep 2009 15:53:01 -0700, miek
wrote:

I'm trying to test a string for alpha chars, if alpha char append char to
a
new string
The following does not work. Is there a tweak I can apply?

l_Alpha_chars = "abcd1"
For q = 1 To Len(l_Alpha_chars)
l_chr = Mid(l_Alpha_chars, q, 1)
If l_chr = [a..z] Then
NewStr = NewStr & l_chr
End If
Next q

Yup, that's a better way. Thanks.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 18 Sep 2009 23:52:51 -0400, "Rick Rothstein"
wrote:

Function IsStringAlpha(s As String) As Boolean
IsStringAlpha = (UCase(s) Like Application.WorksheetFunction. _
Rept("[A-Z]", Len(s))) And Len(s) 0
End Function

This somewhat shorter function will do exactly what your function does...

Function IsStringAlpha(S As String) As Boolean
IsStringAlpha = Not S like "*[!A-Za-z]*" And Len(S) 0
End Function

Thanks these functions are great help

"Chip Pearson" wrote:


Try some code like the following:

Function IsCharAlpha(C As String) As Boolean
IsCharAlpha = UCase(Left(C, 1)) Like "[A-Z]"
End Function

Function IsStringAlpha(s As String) As Boolean
IsStringAlpha = _
(UCase(s) Like Application.WorksheetFunction.Rept("[A-Z]",
Len(s))) _
And _
Len(s) 0
End Function


The IsCharAlpha function tests if a single character C is alpha. The
IsStringAlpha functions tests if a string of characters S is alpha.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)