Formatting SSN
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 just returns whatever is in A1, because a valid SSN cannot begin with "000-".
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, return original value, 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.
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
