Pages

An Excel Function in VBA to add dashes to SSN (social security number)

     Check this out. I've used a Select Case statement to find the length of an existing field that contains a number that represents a social security number (SSN). The function can be used in your spreadsheet to easily change an existing SSN without dashes to to an SSN with dashes.


Function SSN_Add_Dashes(SSN As String)
Select Case Len(SSN)
Case 9
SSN_Add_Dashes = Left(SSN, 3) & "-" & _
Mid(SSN, 4, 2) & "-" & Right(SSN, 4)
Case 8
SSN_Add_Dashes = "0" & Left(SSN, 2) & _
"-" & Mid(SSN, 3, 2) & "-" & Right(SSN, 4)
Case 7
SSN_Add_Dashes = "00" & Left(SSN, 1) & _
"-" & Mid(SSN, 2, 2) & "-" & Right(SSN, 4)
Case 6
SSN_Add_Dashes = "000-" & Left(SSN, 2) _
& "-" & Right(SSN, 4)
Case 5
SSN_Add_Dashes = "000-0" & Left(SSN, 1) _
& "-" & Right(SSN, 4)
Case 4
SSN_Add_Dashes = "000-00-" & Right(SSN, 4)
Case 3
SSN_Add_Dashes = "000-00-0" & Right(SSN, 3)
Case 2
SSN_Add_Dashes = "000-00-00" & Right(SSN, 2)
Case 1
SSN_Add_Dashes = "000-00-000" & Right(SSN, 1)
Case Else
SSN_Add_Dashes = "N/A"
End Select
End Function