Formatting cells for social security #'s

[kcgojnur]

Hello -

I'm trying to create a column(F) in which the user would need to enter their 9 digit social security number without dashes. Therefore if they enter a dash, or don't enter the full 9 digits they should be prompted to do so with a pop up. I tried data validation and entered the following

=AND(LEN(F3)=9,SUM(9-LEN(SUBSTITUTE(F3, CHAR(ROW(INDIRECT("48:57"))), "")))= 9)

this doesn't seem to work b/c if I enter just the 9 digits I still get an error notification.

Any help is greatly appreciated

 

[shg]

You're welcome.

There's no need to quote my posts back to me.

 

[kcgojnur]

Worked perfectly. Thanks again for all your help.

 

[shg]

You're welcome.

 

[Rick Rothstein]

I'm trying to create a column(F) in which the user would need to enter their 9 digit social security number without dashes.

          If cell.Value Like "#########" Then
            cell.Value = Format(cell.Value, "@@@-@@-@@@@")

Worked perfectly. Thanks again for all your help.

Since at the end you are going to display the SSN with dashes, why penalize the user by raising an error message if he/she includes the dashes initially? To allow the user to be able to enter the SSN with or without dashes, the snippet from shg's code shown above could be changed to this...

[table="width: 500"]
[tr]
	[td]          If cell.Value Like "#########" [B][COLOR="#FF0000"]Or cell.Value Like "###-##-####"[/COLOR][/B] Then
            cell.Value = Format([B][COLOR="#FF0000"]Replace([/COLOR][/B]cell.Value[B][COLOR="#FF0000"], "-", "")[/COLOR][/B], "@@@-@@-@@@@")[/td]
[/tr]
[/table]

 

[kcgojnur]

Hi Rick, I removed the "-" from the code in which it now works perfectly