URGENT! check if cell has only ALPHA chars
 

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

URGENT! check if cell has only ALPHA chars
 

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?

--
Regards,
Tom Ogilvy

wrote in message
...
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

URGENT! check if cell has only ALPHA chars
 

hi,
yes. ISTEXT
-----Original Message-----
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
.

URGENT! check if cell has only ALPHA chars
 

sorry i shoulda been more clear!

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


-----Original Message-----
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?

--
Regards,
Tom Ogilvy

wrote in message
...
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


.

URGENT! check if cell has only ALPHA chars
 

wrote:
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]

URGENT! check if cell has only ALPHA chars
 

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



On Wed, 29 Sep 2004 08:28:45 -0700,
wrote:

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