Mail Merge Coding & Number Formatting

Question

I'm creating an industry directory using Excel to Word Mail Merge. I'm struggling with formatting the phone numbers into ###-###-#### or (###) ###-#### structure without it overriding my b: command. I tried using the DDE command process so the formatting would come through Excel to Word but Office support wasn't able to figure out why it wouldn't work after an hour and a half on the phone.

Also, the company names and addresses are duplicating with each entry and I'd like to find the code that will eliminate that problem. I found a few codes online that are similar but I couldn't get any to work. Any help would be appreciated!

Answer 1

The data duplications are likely to be the result of inserting the mergefields multiple times.

The mergefield \b & \f switches don't work with other switches. Presumably you have conditional content to go with the # (e.g. 'Phone:'). In that case you could use a field coded along the lines of:

{IF{MERGEFIELD Phone}<> "" "Phone: {MERGEFIELD Phone # "'('###') '###'-'####"}"}

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field construction are all required.

Answer 2

See "Formatting Word fields with switches" on fellow MVP Graham Mayor's website at

http://www.gmayor.com/formatting\_word\_fields.htm

If the formatting switches are messing with the \b switch (I assume that is what you mean by b: command), you can achieve the same result by using an If...then...Else field construction.

To sort out the issue with the duplications, it would be necessary to see the files.  If you want to send me a copy of the mail merge main document and of the data source, I will investigate the issue.

Answer 3

Now my only problem is having a blank space if there is no data in a cell. So if they didn't enter a fax there is just empty space.

ie

Phone: (###) ###-####

Cell: (###) ###-####

Email:

And last I just need to figure out how to stop the company from repeating...

Suppressing the empty lines requires nothing more than incorporating the line/paragraph breaks into the IF field:

{IF{MERGEFIELD Phone}<> "" "Phone: {MERGEFIELD Phone # "'('###') '###'-'####"}

¶"}{IF{MERGEFIELD Fax}<> "" "Fax: {MERGEFIELD Fax # "'('###') '###'-'####"}

¶"}{IF{MERGEFIELD Cell}<> "" "Cell: {MERGEFIELD Cell # "'('###') '###'-'####"}

¶"}Email: {MERGEFIELD Email}

where ¶ is the line/paragraph break.

For more mailmerge formatting tips, see my Mailmerge Tips and Tricks thread at:

http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

or:

http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

As for the company, I repeat what I said earlier:

The data duplications are likely to be the result of inserting the mergefields multiple times.

Answer 4

That link is awesome! Wish I'd found it months ago.

Answer 5

IT'S WORKING!!!!!

Now my only problem is having a blank space if there is no data in a cell. So if they didn't enter a fax there is just empty space.

ie

Phone: (###) ###-####

Cell: (###) ###-####

Email:

And last I just need to figure out how to stop the company from repeating...