Results 1 to 6 of 6

Hide characters in SSN#

This is a discussion on Hide characters in SSN# within the Excel Questions forums, part of the Question Forums category; Can anyone tell me how to enter a full SSN# but only display the last four digits. In other words ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    21

    Default Hide characters in SSN#

    Can anyone tell me how to enter a full SSN# but only display the last four digits. In other words enter 111-11-1111 but after you enter the number, it displays as xxx-xx-1111?

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Hide characters in SSN#

    Put the following in the relevant sheet module (e.g. if your input is in Sheet 1, then put the macro in that sheet module).

    The macro is set for range A1:A20. Amend to suit.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target(1), Range("A1:A20")) Is Nothing Then _
    Target(1).Value = Right(Target, 4)
    End Sub

    HTH

    Mike

  3. #3
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default Re: Hide characters in SSN#

    I would suggest hiding the original column with the full SS#'s and in the adjacent column put the following formula:

    ="xxx-xx-"&right(C1,4)

    assuming the hidden column is C and data begins in row 1

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Hide characters in SSN#

    Buddie is absolutely right. I misinterpreted the question – the display is to show “xxx-xx-1111”. In addition, it makes sense putting the original SSN in one column and formatting it in another.

    Amended macro:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target(1), Range("A1:A20")) Is Nothing Then _
        Target(1).Offset(0, 1).Value = "xxx-xx-" & Right(Target, 4)
    End Sub
    Regards,

    Mike

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Hide characters in SSN#

    Or, in line with BuddieB's idea, if the full SS# is entered in C1, then in an adjacent cell enter ...

    =--(RIGHT(C1,4))

    Custom Number Format it as "xxx-xx-"0000

    and I would hide the entry in column C and protect the worksheet, and/or hide column C all together.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    New Member
    Join Date
    Jun 2002
    Posts
    21

    Default Re: Hide characters in SSN#

    I will do that. Thank you for everyone's help.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com