aLPHAnUMERIC validation

[[email protected]]

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?

<[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Roland]

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


"[email protected]" 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
>
>

[[email protected]]

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.

[JMB]

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

"[email protected]" 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
>
>

[Ron Coderre]

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


"[email protected]" 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.
>
>