testing whether a character is a letter or number

  • Thread starter Thread starter Paul James
  • Start date Start date
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
 
set rng = Worksheets("dataEntry").Range("testCell")


if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) <> ucase(mid(rng(2,1)) then
 
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)
 
a$=yourcell

numvalue= asc(left(a$,1))+asc(mid(a$,1))
if numvalue >9+9 then combination is false
 
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

??
 
Wouldn't this return true if the entry was either 1a or a1, (which isn't
what I need)?
 
if rng = "" or _
( isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) <> ucase(mid(rng(2,1))) then
 
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
 
Back
Top