Test for alpha char

[miek]

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

 

[Ron Rosenfeld]

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

 

[Chip Pearson]

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)

 

[Rick Rothstein]

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)

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)

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

 

[Chip Pearson]

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)

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

 

[miek]

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

 

[miek]

Thanks these functions are great help

 

[Ron Rosenfeld]

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