# Thread: Mail Merge - merging part of a field (2000/xp)

1. ## Mail Merge - merging part of a field (2000/xp)

I need help with merging. The admissions office needs to send acceptance letters to new students. Letters currently include complete social security numbers. Datafile is generated from mainframe then merged in Word. Problem now is that the letters cannot show complete social numbers. They only need the last 4 numbers. I thought the numbers would be download in separate fields xxx then xx then xxxx - however they come across as 123-45-6789 so I am having problems merging only the last 4 numbers. Can anyone help?

2. ## Re: Mail Merge - merging part of a field (2000/xp)

Any chance of having the mainframe generate a column with the last four digits of the SSN?

If that is not feasible, can you open the data file in Excel and add a column with formulas to extract the last four digits of the SSN?

3. ## Re: Mail Merge - merging part of a field (2000/xp)

<P ID="edit" class=small>(Edited by macropod on 05-Apr-03 16:10. Field calculation simplified)</P>Hi Melanie,

Splitting out just the last 4 digits of the field is difficult, though not impossible. The main problem is that you've got hyphens in the numbers. If that were not so, a relatively simple set of INT and MOD finctions applied to the mergefield would work. But, since you do have hyphens, a fairly convoluted workaround is needed.

Below is the syntax for a field that would return
The parts of 123-45-6789 are 123, followed by 45, then 6789.
from the hyphenated number. For your mergefield, you'd only be interested in:
{QUOTE{SET ID"123-45-6789"}{SET EXP3{={=-{=-{ID}-ID}/2}+(({ID}*(-1)-{ID})*(-1)/2+{ID})*(-1)}}{EXP3}}
in which you'd replace {SET ID"123-45-6789"} with {SET ID {MERGEFIELD SS#}} or its equivalent, where SS# is the Social Security Number record.

Cheers

PS: the braces {} are entered via Ctrl-F9, not from the keyboard - full field attached. The line feeds in the field are unnecessary and are just there for clarity.

{QUOTE

4. ## Re: Mail Merge - merging part of a field (2000/xp)

Ok - I am thinking that maybe I could do a search and replace the hyphens with something else? maybe spaces and then separate the SS#'s this way? I am guessing here. This solution has to be easy for the user to work with. They basically know how to merge. They send out admissions letters on a regular basis so I do not know if the field code solution will work for them. I am a bit confused by it myself (though that isn't saying much!!!)
Any other help would be greatly appreciated.

5. ## Re: Mail Merge - merging part of a field (2000/xp)

Hi Melanie,

Yes, you probably could do a search and replace, but the solution I posted avoids the need for that.

In your current document, I assume you have a mergefield that looks something like:
{MERGEFIELD SSN}
where SSN is the Social Security Number.
If you replace that field with:
{QUOTE{SET ID{MERGEFIELD SSN}}{SET Last4Digits{={=-{=-{ID}-ID}/2}+(({ID}*(-1)-{ID})*(-1)/2+{ID})*(-1)}}{Last4Digits}}
only the last 4 digits will be displayed (eg 123-45-6789 becomes 6789). If you want to display something like XXX-XX-6789, you'd put

6. ## Re: Mail Merge - merging part of a field (2000/xp)

Thank you so much for this information - I thought I had succesfully put this project to rest - problem though. After I changed this field on approx 50 letters, I still have a problem. It seems I cannot copy and paste this line of code to each of the letters, it does not display properly. What could I be doing wrong? Do I need to individually use a control+f9 to insert the brackets?

7. ## Re: Mail Merge - merging part of a field (2000/xp)

Hi Melanie,

If you were copying & pasting from the word document attached to my last post, it should work OK. But if you were copying & pasting from the listing in the last post then, yes, you'd have to convert the braces to field codes via Ctrl-F9, getting the correct nesting structure, plus delete the typed braces. Much easier to copy & paste from the word document.

Cheers

PS: Experiment with one document to make sure you've got it working before doing the rest - less pain that way if you run into more problems.

8. ## Re: Mail Merge - merging part of a field (2000/xp)

Thanks for the info. I will work on it today. Funny thing is I had it working in the first two documents. I double checked before I started on the rest of them. Thanks again for all your help.
Melanie

9. ## Re: Mail Merge - merging part of a field (2000/xp)

Hello - I thought all was well, I have not heard from the admissions office for two weeks. They called this morning and said they realized if the last 4 numbers of the social security start with 0, such as 0123, then only 123 appear in the field. They need the 0. How do I fix this? thanks!!!!

10. ## Re: Mail Merge - merging part of a field (2000/xp)

I believe you will need to use the Format switch and tell it to display the number as a 4-digit number with leading zeros.

11. ## Re: Mail Merge - merging part of a field (2000/xp)

Hi Melanie,

You can fix that by changing the last part of the field to include a numeric picture switch, as per Wendell's suggestion. From my previous postings, change:
{QUOTE{SET ID{MERGEFIELD SSN}}{SET Last4Digits{={=-{=-{ID}-ID}/2}+(({ID}*(-1)-{ID})*(-1)/2+{ID})*(-1)}}{Last4Digits}}
to
{QUOTE{SET ID{MERGEFIELD SSN}}{SET Last4Digits{={=-{=-{ID}-ID}/2}+(({ID}*(-1)-{ID})*(-1)/2+{ID})*(-1)}}{Last4Digits <font color=red># 0000</font color=red>}}

Cheers

12. ## Re: Mail Merge - merging part of a field (2000/xp)

Wow - I will try that - thank you so much. You guys are the best!

#### Posting Permissions

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