Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro in XL (XL 2000)

    Hello, I wonder if there is an easy macro for my "problem". I have a list of employees with info i.e. Name/Addr./ etc. and SSN going down in a column. It shows the whole SSN, so all 9 digits but no dashes or spaces: 123456789. The macro I'd like to create is that it goes down the column and deletes the first 5 digits so it will show only the last 4 digits. It should go all the way down, meaning, if one sheet has 100 rows and another sheet has 150 rows the macro should go as far as needed. Your help is greatly appreciated. Thank you, Tira

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro in XL (XL 2000)

    Hi Tira,

    While this is easy enough to do with a macro, you could also use a formula. For example, say your SSN is in A1, the follwowing formula would list just the last 4 digits in B1:

    =TEXT(RIGHT(A1,4),"0000")

    This may be useful if you want to retain the whole SSN in a hidden column, but only display the last 4 digits.

    Alternatively, you could use a custom format to display only the last 4 digits in Column A, without resorting to hidden columns or deleting data. To do this, select a cell containing the SSN. Then, in Format|Cells|Number|Custom, type:
    1. Six 0s (i.e. 000000), followed by
    2. Alt-010, followed by
    3. Four 0s (i.e. 0000)

    Now format the cell to wrap text with the vertical alignment set to 'Bottom'

    Finally, increase the row height a small amount so as to force the cell to display only the 'last' line - on my system, increasing from 13.2 to 14 was enough to achieve this. You may need to experiment a bit, but you should end up with the required display. Note that you'll still be able to see the original unformatted number in the formula bar.


    Both of the above solutions avoid problems that might be caused by duplicated sets of the last 4 digits.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Macro in XL (XL 2000)

    Hi Hans,

    Not wanting to rain on your parade but, unless you format the result as text, or use a custom numeric format, your solution will lose leading zeros.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro in XL (XL 2000)

    Here is a method that doesn't use a macro. I have assumed that your SSN's are numeric; I will indicate what to change if they are text. And let's say that the SSN's are in D1100.
    If necessary, insert an empty column to the right of the SSN column.
    In the cell next to the first SSN, enter the formula =MOD(D1,10000); use =RIGHT(D1;4) for text.
    Double click the little black square in the lower right corner of the cell with the formula. This will fill down the formula as far as needed.
    Without undoing the selection, select Edit | Copy, then Edit | Paste Special, Values, OK.
    Finally, select the original SSN's and delete the selection.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro in XL (XL 2000)

    Yes, I realize that. Thanks for the heads-up.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro in XL (XL 2000)

    Hans and macropod, Thank you both for your help. I tried both "versions" and it worked perfectly. Since it's easier for me to remember something like that when I understand what's happening, could you explain what MOD stands for? As for the macro, how "easy" would that be?? Thank you for your time, Tira

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro in XL (XL 2000)

    here is a simple macro, that returns the last 4 as text. If you want it as a number, it would have to be changed and you would probably need to format the cell to disply leading zeroes. I am a firm believer tha SSN and PhoneNumbers and zipcodes, credit card numbers, etc, are not numbers but text. Numbers are things you manipulate with matehmatical functions. None of the above meet that criteria, so it should be text.

    <pre>Sub ShowLast4Text()
    Dim rCell As Range
    For Each rCell In Selection
    rCell = "'" & Format(Right(rCell, 4), "0000")
    Next
    End Sub</pre>


    The Mod is the remainder after a number is divided by the divisor
    =MOD(5, 2) = 1 since 5/2 = 2 remainder 1

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro in XL (XL 2000)

    The MOD function (abbreviation of modulo) returns the remainder of the first argument after division by the second argument. So MOD(17,5) = 2 since 17 divided by 5 gives 3, with a remainder of 17 - 3x5 = 2.

    Here is a macro. You must select the first cell to be modified; the macro will automatically continue until the last filled cell in the same column.

    Sub GetLast4()
    Dim oRange As Range, oCell As Range

    Set oRange = Range(ActiveCell, Cells(65536, ActiveCell.Column).End(xlUp))
    For Each oCell In oRange.Cells
    oCell.Value = Right(oCell.Value, 4)
    Next oCell

    Set oCell = Nothing
    Set oRange = Nothing
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro in XL (XL 2000)

    Steve and Hans, again thank you much for the macros. They worke great, do just what I need them to. I appreciate your help. Thanks, Tira

Posting Permissions

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