Format SSN to last 4 digits

[jennvkerr]

Hi there! I have a column with social security numbers, i.e. 555-33-2222 and I need to change to show only the last four digits, i.e. xxx-xx-2222. Can this be done in excel?

Thanks so much!!

 

[VoG]

Try

="xxx-xx-"&RIGHT(A1,4)

 

[Rick Rothstein]

Hi there! I have a column with social security numbers, i.e. 555-33-2222 and I need to change to show only the last four digits, i.e. xxx-xx-2222. Can this be done in excel?

Are you looking to hide the beginning part of SSN within the cells that contain them or, as VoG has done, display the modified SSN in some other cell? If you wanted to hid the beginning part within their cells, then you do realize the full SSN will be displayed in the Formula Bar when that cell is selected (or will you have selection of that column turned off)?

 

[jennvkerr]

Are you looking to hide the beginning part of SSN within the cells that contain them or, as VoG has done, display the modified SSN in some other cell? If you wanted to hid the beginning part within their cells, then you do realize the full SSN will be displayed in the Formula Bar when that cell is selected (or will you have selection of that column turned off)?

Its ok if the full SSN appears in the formula bar... just when printing i need those other characters hidden. thank you!

 

[Rick Rothstein]

Its ok if the full SSN appears in the formula bar... just when printing i need those other characters hidden. thank you!

First off, I am assuming your SSN's have been entered as 9-digit numbers and then formatted to look like an SSN (otherwise they would be text and you cannot change the cell format of text). If that is the case (or if you just use Excel's Replace dialog box to remove the dashes to you have real numbers in the column), then you can use this macro to toggle back-and-forth between full SSN's and SSN's encoded with the X's...

Sub ToggleVisibilityOfSSN()
  Dim X As Long, LastRow As Long
  Const SSNcolumn As String = "C"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, SSNcolumn).End(xlUp).Row
  For X = StartRow To LastRow
    If Mid(Cells(X, SSNcolumn).NumberFormat, 2, 1) = "X" Then
      Cells(X, SSNcolumn).NumberFormat = "000-00-0000"
    Else
      Cells(X, SSNcolumn).NumberFormat = """XXX-XX-" & Right(Cells(X, SSNcolumn).Value, 4) & """"
    End If
  Next
End Sub

EDIT NOTE
---------------
I forgot to mention... you have to verify the assumed column letter designation and start row for your SSN's. They are located in the Const (constant) statements.