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

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

wrote in message
ups.com...

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

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


" wrote:


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

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.

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

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


" wrote:

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.