Data Validation in Excel 2007

I need to set up a data validation for a Social Security Number. The number
must be 9 digits long, no dashes and no letters. I am using Excel 2007.
--
Bruce

Roger
Tue May 08 11:02:38 CDT 2007

Hi Bruce

Try
=AND(LEN(A1)=9,ISNUMBER(A1))

--
Regards

Roger Govier


"BruceY" <byellin@discussions.microsoft.com> wrote in message
news:8598905D-F2E3-4CF5-AE0D-369FADF7E530@microsoft.com...
>I need to set up a data validation for a Social Security Number. The
>number
> must be 9 digits long, no dashes and no letters. I am using Excel
> 2007.
> --
> Bruce

byellin
Tue May 08 13:28:01 CDT 2007

Thanks for your quick reply,

Unfortunately, when a Social Security Number starts with a 0, I get the
error message. I assume this is because your formula is asking for a numeric
verification and Excel does not recognize a number with a leading 0 as
numeric. Do you have any other suggestions?

Thanks for your help,

Bruce
--
Bruce


"Roger Govier" wrote:

> Hi Bruce
>
> Try
> =AND(LEN(A1)=9,ISNUMBER(A1))
>
> --
> Regards
>
> Roger Govier
>
>
> "BruceY" <byellin@discussions.microsoft.com> wrote in message
> news:8598905D-F2E3-4CF5-AE0D-369FADF7E530@microsoft.com...
> >I need to set up a data validation for a Social Security Number. The
> >number
> > must be 9 digits long, no dashes and no letters. I am using Excel
> > 2007.
> > --
> > Bruce
>
>
>

Peo
Tue May 08 14:18:04 CDT 2007

You probably need an event macro, do a Google search for [event macro data
validation social security numbers]


--
Regards,

Peo Sjoblom


"BruceY" <byellin@discussions.microsoft.com> wrote in message
news:B75339B8-48EA-4162-9F67-DE342A7FD3AE@microsoft.com...
> Thanks for your quick reply,
>
> Unfortunately, when a Social Security Number starts with a 0, I get the
> error message. I assume this is because your formula is asking for a
> numeric
> verification and Excel does not recognize a number with a leading 0 as
> numeric. Do you have any other suggestions?
>
> Thanks for your help,
>
> Bruce
> --
> Bruce
>
>
> "Roger Govier" wrote:
>
>> Hi Bruce
>>
>> Try
>> =AND(LEN(A1)=9,ISNUMBER(A1))
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "BruceY" <byellin@discussions.microsoft.com> wrote in message
>> news:8598905D-F2E3-4CF5-AE0D-369FADF7E530@microsoft.com...
>> >I need to set up a data validation for a Social Security Number. The
>> >number
>> > must be 9 digits long, no dashes and no letters. I am using Excel
>> > 2007.
>> > --
>> > Bruce
>>
>>
>>

Erny
Tue May 08 15:29:55 CDT 2007

Is it to draw attention to an error or do you wish to force someone to use
the format you specify?

If it is to draw attention, you might consider formatting the corresponding
column to text and using conditional formatting with:

=ISVAL(VALUE(A1))*LENGTH(A1)<>9
and choosing for instance colour "red" if the condition is true. Note that
this will also colour the unused cells; you could avoid this by adding the
factor (A1<>"") in front of the whole:

=(A1<>"")*(ISVAL(VALUE(A1))*LENGTH(A1)<>9)

Kind regards,
Erny

"BruceY" <byellin@discussions.microsoft.com> schrieb im Newsbeitrag
news:8598905D-F2E3-4CF5-AE0D-369FADF7E530@microsoft.com...
>I need to set up a data validation for a Social Security Number. The
>number
> must be 9 digits long, no dashes and no letters. I am using Excel 2007.
> --
> Bruce

Debra
Tue May 08 16:52:48 CDT 2007

Format the cell as Text, and modify Roger's formula slightly:

=AND(LEN(A1)=9,ISNUMBER(--A1))

BruceY wrote:
> Thanks for your quick reply,
>
> Unfortunately, when a Social Security Number starts with a 0, I get the
> error message. I assume this is because your formula is asking for a numeric
> verification and Excel does not recognize a number with a leading 0 as
> numeric. Do you have any other suggestions?
>
> Thanks for your help,
>
> Bruce


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

byellin
Wed May 09 08:21:02 CDT 2007

Debra,

This worked great. Thanks so much for your help.
--
Bruce


"Debra Dalgleish" wrote:

> Format the cell as Text, and modify Roger's formula slightly:
>
> =AND(LEN(A1)=9,ISNUMBER(--A1))
>
> BruceY wrote:
> > Thanks for your quick reply,
> >
> > Unfortunately, when a Social Security Number starts with a 0, I get the
> > error message. I assume this is because your formula is asking for a numeric
> > verification and Excel does not recognize a number with a leading 0 as
> > numeric. Do you have any other suggestions?
> >
> > Thanks for your help,
> >
> > Bruce
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>