Formatting Word will merging from Excel

[Susan Altemus]

Because merged data from Excel doesn't retain formatting, I need to format my main Word document using "switches", but I have no idea how to do that.

For example I merge 34987.25 and want it displayed as $34,987.25 or a date like 7/7/09 as July 7, 2009.

Any suggestions?

Susie Altemus

 

[Joe4]

Because I like Excel a lot better in Word, I often format the fields in Excel the way I want by converting them to text before doing the Mail Merge. I find it easier than working with Word Switches.

Here is how you would do that.

For your dollar number, if your number is in cell A1, then use a formula like this:
=TEXT(A1,"$0,000.00")

For your date, if that date is in cell B1, then use a formula like this:
=TEXT(B1,"mmmm d, yyyy")

Then just use these calculated fields in your merge.

 

[Susan Altemus]

Are you saying that I need to insert a column by every column that I need to format where I would insert your recommendations?

 

[Joe4]

Yes, you would need to do that in another column (unless the values in those cells are already calculations, in which case you could just add the formula to your existing formula).

 

[Susan Altemus]

Thank you.

 

[Macropod]

Hi Susan,

There are two a far simpler ways than re-formatting the data in Excel :
1. Use a DDE connection for the mailmerge. This is usually sufficient;
2. Apply formatting switches to the mailmerge main document.
In any event, re-formatting the data in Excel is ridiculous and is not guaranteed to work!!

Here's how to control the formatting by adding switches to the mailmerge main document:

Mailmerge Number & Currency Formatting

To control number & currency formatting, add a numeric picture switch to the mergefield. To do this:
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it.

Note: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values


The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

Mailmerge Date Formatting

To get the date format you want, you can add a date picture switch. In Word:
. select the mergefield;
. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch today's date will come out as '3 March 2010'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.
. position the cursor anywhere in this field and press F9 to update it.


If you post back with the details of the kind of any other data fields you're having problems with (eg percentage, US Zip Codes), I'll give you some more instructions on how to address those issues via field coding in the mailmerge main document.

 

[Joe4]

There are two a far simpler ways than re-formatting the data in Excel :
1. Use a DDE connection for the mailmerge. This is usually sufficient;
2. Apply formatting switches to the mailmerge main document.
In any event, re-formatting the data in Excel is ridiculous and is not guaranteed to work!!

Can't say I totally agree with your statements here. Note my disclaimer in my first reply:

Because I like Excel a lot better in Word, I often format the fields in Excel the way I want by converting them to text before doing the Mail Merge. I find it easier than working with Word Switches.

I work with Access, Excel, and Word, and I will make no bones that I have a much greater comfort zone with Excel than I do Word (and I would dare say most people on this forum do). I am a programmer, and have done lots of mail merges (though not as many as I used to do). I find working with merge fields in Word (editing them and adding switches) a painful and cumbersome process. Maybe it is because I don't work with Word as much as I work with Excel and Access.

Regarding re-formatting the data in Excel, I can honestly say that I have NEVER had an instance it which it did not work, and I can't even fathom a situation in which it wouldn't work. Can you think of an example? By using the Text function, you are converting the value to Text. When merging into Word, Word just returns that exact Text value. One of the reasons why I like this is because I know that if I do this, it will display in Word EXACTLY as it appears in Excel.

Am I saying that it is better to do it the way that I proposed than to use Word switches? No, all things being equal, I think using the Word Switches is the better way to go. However, for many people that I know, (including myself) things are not equal. My knowledge and comfort level in Excel is substantially more than my comfort level and knowledge for Word. So I was providing an alternative solution of how I have dealt with this from my perspective.

 

[Susan Altemus]

Thank you both. I think I will attempt the switching method.

Thank you so much.

 

[Macropod]

Hi Joe,

In all likelihood, the problems you experienced in the past were occassioned by a lack of understanding on how the different data connection methods work. Some connection methods ignore the formatting and give access to the underlying values (eg dates come out as numbers( whilst DDE, in particular, tends to retain the data type (and most of the formatting). Allied with this is that, with some connection methods, Word interrogates the first 8-15 data rows to 'guess' what kind of data each field contains and, if the data are mixed, or missing from some rows, Word sometimes gets it wrong. Formatting all the data as text makes little difference to how Word interprets it, since the formatting is ignored.

I would strongly recommend investing the little time required to master the use of field switches. Using them is much more efficient (and reliable) than duplicating the data. Plus, the field switches give you the freedom to represent the data one way in the source (ie the way that works best for storage and analysis) and multiple different ways in the mailmerge.

 

[Joe4]

Fair points, but as you can see from your explanation, it is a bit more involved and not quite as simple and straighforward as one would like it to be.

Nowadays, I only do a few mail merges a year now, so I am afraid that if I took the time to learn it, by the time I would need it, I probably will have forgotten most of it. Like so many other things, if you don't use it regularly, you lose it!

Word interrogates the first 8-15 data rows to 'guess' what kind of data each field contains and, if the data are mixed, or missing from some rows, Word sometimes gets it wrong. Formatting all the data as text makes little difference to how Word interprets it, since the formatting is ignored.

I can't say that I have experienced this behavior myself. Every time I have used the TEXT function to format it, it comes out that way in Word without doing any sort of re-formatting. As matter as fact, Word "guessing" the format is usually more problematic, because as you say, it often guesses wrong.

One of the advantages to my method is that I can view all the data in one placel, and know that is how it is going to show up in Word (at least that has been my experience). So I can easily spot and data issues simply by looking at one data file. If I do switches, and somewhere down the list, Word guesses wrong, and puts it in a bad format, I would not be able to see that easily. I would need to look at each and every merged field on each and every page. Some of our merged documents are over 1000 pages long, so that really isn't too feasible.

Microsoft Access has the same format "guessing" issue when trying to import Excel files. And their error reporting is awful. All that it does is tell you that there is some sort of error, but gives you no indication of which field or record it is, and then it doesn't import anything. You can spend hours trying to figure out how Access is reading it, and trying to determine which field/record are causing the problem. Inevitably, it is usually quicker to export the data to a Text file, and import that into Access where you get to specify what the format of each field is.

That is my major complaint with the MicroSoft Office products, especially with Word. They try to make these things so user friendly, that they try to do most of the thinking for you. And inevitably, they guess wrong enough times to drive you crazy. As a programmer, my mind-set is I want the applications to do what I tell them, not try to guess what I might want to do.