testing whether a character is a letter or number

P

Paul James

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
 
T

Tom Ogilvy

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


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

Bob Phillips

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)
 
D

dadum1

a$=yourcell

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

Paul James

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

??
 
P

Paul James

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

Tom Ogilvy

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

Paul James

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
Click to expand...

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
 
Top