URGENT! check if cell has only ALPHA chars

[Guest]

hi,

is there a way to check if a cell has ONLY alpha
characters entered into it?

i know there is 'isNumber' but is there something for
letters?

thanks

 

[Tom Ogilvy]

IsText checks if it is stored as text.

But numbers can be stored as text as well.

What is your definition of alpha characters. Is AB234 considered to be
alpha characters?

What about special characters like punctuation and other characters besides
the unendorned letters of the alphabet and numbers?

 

[Guest]

hi,
yes. ISTEXT

 

[Guest]

sorry i shoulda been more clear!

only alphabet (upper/lower) abcd...xyz and ABC...XYZ

 

[Jake Marx]

sorry i shoulda been more clear!

only alphabet (upper/lower) abcd...xyz and ABC...XYZ

This will work, but it's not very elegant:

=25*LEN(A1)=SUM(LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($A$65:$A$90)),"")))

This must be array-entered, so use Ctrl+Shift+ENTER instead of ENTER when
you're done typing or pasting the formula into the formula bar.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Myrna Larson]

You posted this in the programming newsgroup. Are you looking for a VBA
solution? If so, if you want to restrict to the letters A-Z (upper or lower
case)

Function AllLetters(sText As String) As Boolean
Dim Bytes() As Byte
Dim i As Long

Bytes = UCase$(sText)
For i = 0 To UBound(Bytes(), 1) Step 2
If Bytes(i) < 65 Or Bytes(i) > 90 Then
AllLetters = False
Exit Function
End If
Next i
AllLetters = True
End Function

If you want to allow other characters besides A-Z, you could set up a Select
Case block instead of the If statement

Select Case Bytes(i)
Case 65 to 90, 32, 46, 44 'letters, space, period, and comma
'above are OK, don't do anything
Case Else
AllLetters = False
Exit Function
End Select