Forced Format

S

Scott Hutchinson

I'm needing to have another user fill in a field of SSN's for their people on
a document. However I can format the field to be special or custom and it
will correctly show 000-00-0000. Problem is they can enter the following
errors and it will display the result (see below examples entry:result)

abc:abc
1234567890:1234-56-7890

I need to FORCE this field (column) to only accept the 9 digits no alpha and
seperated correctly by the dashs. Any thoughts?

Thanks,
 
B

Bernie Deitrick

Scott,

Select your cells, and use Data | Validation.... Custom.. with the formula
(example for all of column B selected)

=AND(LEN(B1)<=9,ISNUMBER(B1))

The <= on length is necessary because SSNs can start with leading 0s, which
will confound trying to force the length to exactly 9.

HTH,
Bernie
MS Excel MVP
 
S

Scott Hutchinson

This worked absolutely perfect for me! Thanks so much. I only had to add in
an error message for them :)
 
Top