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