testing whether a character is a letter or number
 

I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

testing whether a character is a letter or number
 

set rng = Worksheets("dataEntry").Range("testCell")


if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

--
Regards,
Tom Ogilvy

"Paul James" wrote in message
...
I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this
case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

testing whether a character is a letter or number
 

Paul,

Try this code

With Worksheets("dataEntry").Range("testCell")
If IsNumeric(Left(.Value, 1)) Then
If IsText(Mid(.Value, 2, 1)) Then
Debug.Print "Yes"
End If
End If
End With


Private Function IsText(val) As Boolean
IsText = (val = "a" And val <= "z") Or _
(val = "A" And val <= "Z")
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul James" wrote in message
...
I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this
case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

testing whether a character is a letter or number
 

"Tom Ogilvy" wrote in message
...


if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then


Smart!

testing whether a character is a letter or number
 

a$=yourcell

numvalue= asc(left(a$,1))+asc(mid(a$,1))
if numvalue 9+9 then combination is false


---
Message posted from http://www.ExcelForum.com/

one additional question
 

My thanks to Tom and Bob.

One other question:

I'd also like the expression to return true if the cell has no characters of
any type in it. Can I do that by saying

if rng < "" and_
if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

??

testing whether a character is a letter or number
 

Wouldn't this return true if the entry was either 1a or a1, (which isn't
what I need)?



"dadum1 " wrote in message
...
a$=yourcell

numvalue= asc(left(a$,1))+asc(mid(a$,1))
if numvalue 9+9 then combination is false


---
Message posted from http://www.ExcelForum.com/

one additional question
 

if rng = "" or _
( isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1))) then

--
Regards,
Tom Ogilvy


Paul James wrote in message
...
My thanks to Tom and Bob.

One other question:

I'd also like the expression to return true if the cell has no characters
of
any type in it. Can I do that by saying

if rng < "" and_
if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

??

one additional question
 

if rng = ""

Of course - Duh.

Thanks, Tom.

one additional question
 

Tom - the code you wrote works great:

if rng = "" or _
( isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1))) then

I did find a typo in the last expression - (the one to the right of the
inequality):

ucase(mid(rng(2,1)) should be written as ucase(mid(rng,2,1))

Thanks again for your help.

Paul