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.

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 (18 KB)

Site last updated: April 19, 2014

Random Data Generator