Data Validation - Social Security #s

[newbie]

Hi my first post - hope this makes sense.


I need to setup a data validation routine so users can enter ss # in the the standard format (including #s starting with 0) ie


xxx-xx-xxxx


I had setup the spreadsheet using the custom format but the users started entering values without the 'dashes' which messes up the analysis. Searched the net and found this validation but this one is without the dash'-'


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

 

[keymaster]

Ok.. try this...


=AND(LEN(A1)=11,ISNUMBER(--SUBSTITUTE(A1,"-","")),MID(A1,4,1)="-",MID(A1,7,1)="-") as the custom data validation formula in data validation...

 

[newbie]

Chandoo,

You're simply the best - thanks a million - it works like a charm !