aLPHAnUMERIC validation

[pjaganathan]

Hi

How do I validate whether a data entered in a cell in alphanumeric? I
dont find any functions available...Is there someway to do using the
existing functions??

Thanks
Pras

 

[Barb Reinhardt]

Are you saying you want only letters and numbers in the cell?

 

[Guest]

PJ,

Assume your data is in cell A1.

Choose from one of these three.

In cell B1 type =ISTEXT(A1), or

=ISNUMBER(A1), or

=OR(ISTEXT(A1),ISNUMBER(A1))

 

[pjaganathan]

Yeah I want only letters and numbers in my cell.

ISTEXT returns true even if the cell contains something like w! where !
is not an alphabet.
Further the cell might contain both number and alphabet, like "nf45m".
In that case
none of above would work.

Thanks
Pras.

 

[Guest]

Assuming your data is in cell A1

=SUM((--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<65))+(--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>90)))=0

confirmed with Cntrl+Shift+Enter

If you want to ignore spaces (so "Joe Smith" is treated as text even though
it has a space) then:

=SUM((--(CODE(UPPER(MID(SUBSTITUTE(A1,"
",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"
","")))),1)))<65))+(--(CODE(UPPER(MID(SUBSTITUTE(A1,"
",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))>90)))=0

also confirmed w/Cntrl+Shift+Enter

 

[Guest]

I believe this works:
To test cell A1, try this formula:
B1:
=ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz0123456789")))

That formula is not case-sensitive and returns TRUE if the cell only
contains letters and/or numbers. It returns FALSE if blank or if it contains
special characters.

Does that help?

***********
Regards,
Ron