# Thread: Macro in XL (XL 2000)

1. ## 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. ## 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

3. ## 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

4. ## 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. ## Re: Macro in XL (XL 2000)

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

6. ## 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. ## 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. ## 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. ## 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
•