Data Validation in Excel 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
You probably need an event macro, do a Google search for [event macro data
validation social security numbers]
 
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
 
Format the cell as Text, and modify Roger's formula slightly:

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