Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    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.
    Wendell

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

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •