Formatting SSN on your worksheet

The formula to convert cell values to SSN is:

=IF(AND(LEN(A1)>6, LEN(A1)<9, ISERROR(FIND("-",A1))),
REPT("0",9-LEN(A1))&LEFT(A1,LEN(A1)-6)&"-"&MID(A1,LEN(A1)-5,2)&"-"&RIGHT(A1,4),
IF(AND(LEN(A1)>6,LEN(A1)<9,NOT(ISERROR(FIND("-",A1)))),A1,LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4)))

Assuming you have a number between 7 and 9 digits in cell A1, this formula will change it to a real SSN, complete with dashes in the appropriate places. Simply fill down as needed if you have a whole column that needs to be converted. If you only have 6 or less digits in a cell, this formula displays "", because a valid SSN cannot begin with "000-". Or, instead of leaving blanks in the column, you could change this behavior by changing the "" in the third row above to A1.

ex: =IF(AND(LEN(A1)>6, LEN(A1)<9, ISERROR(FIND("-",A1))),
REPT("0",9-LEN(A1))&LEFT(A1,LEN(A1)-6)&"-"&MID(A1,LEN(A1)-5,2)&"-"&RIGHT(A1,4),
IF(AND(LEN(A1)<7,LEN(A1)>9,NOT(ISERROR(FIND("-",A1)))),A1,LEFT(A1,3)&"-"&MID(A1,4,2)&"-"&RIGHT(A1,4)))

and it would simply return whatever was in A1 if there was less than 7 or more than 9 digits.

I have split the formula visually so you can see that it uses a simple IF formula. First it checks that the cell is 7 or 8 non-zero digits (the only appropriate number of non-zero numbers in a SSN) and that there aren't already dashes in the cell. If it meets all those conditions, it uses the REPT function to pad the front of the cell with zeroes. If the cell is less than 7 or more than 9, display nothing, else it must be 9 characters so just format normally.

And here is the VBA code that can be used to toggle between a regular number and SSN. Beware of the leading zero when you remove dashes from a SSN, it may be dropped after you run this code.

Sub Toggle_SSN()
Dim cell As Excel.Range
Application.ScreenUpdating = False

If MsgBox("Are you converting from XXX-XX-XXXX to XXXXXXXXX?" & vbCr & _
"If you are switching from XXXXXXXXX to " & "XXX-XX-XXXX then click 'No'") _
= vbYes Then
   For Each cell In Selection
      cell = Left(cell, 3) & Mid(cell, 5, 2) & Right(cell, 4)
   Next cell
Else
   For Each cell In Selection
      cell = Left(cell, 3) & "-" & Mid(cell, 4, 2) & "-" & Right(cell, 4)
   Next cell
End If

Application.ScreenUpdating = True
End Sub

Download sample workbook which contains the formula and VBA code.