Forced Format

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

Guest

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,
 
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
 
This worked absolutely perfect for me! Thanks so much. I only had to add in
an error message for them :-)
 
Back
Top