mail merge from excel whilst maintaining format

**mania112** · 11-03-2009, 11:28 AM

i need to create a mail merge in word which uses data in an excel spreadsheet.

some of the cells are currently set as a date and others as a currency.

is there a way to maintain these formats when its merged into word?

at the moment it moves dates into numbers and currency into numbers without commas and pound signs.

many thanks

**Andy Pope** · 11-03-2009, 11:35 AM

Do the formatting in excel by using a helper column to format the values.
So if A2 contains a date use a helper column with the formula

=TEXT(A2,"dd/mmm/yyyy")

Mail merge using the text helper column

**teylyn** · 11-03-2009, 04:12 PM

Alternatively, you can use the formatting switches in the mergefield in Word

- Hit ALT-F9 to show all field codes
- for a merge field called Date1, you'll see something like

{ MERGEFIELD "Date1" }

- Apply formatting to this field using the time-date switch \@ and specify your date format in double quotes. Note that you must use capital M for month. To display your date as 24-Dec 2009, for example, use the switch

{ MERGEFIELD "Date1" \@ "dd-MMM yyyy"}

- for currency values, use the numeric switch \# followed by the number format (no double quotes required). Something like

{ MERGEFIELD "amount1" \# $#,###.00 }

If you want to ensure that the merge field has the same font and size, add the \* MERGEFORMAT switch.

HTH

**kjb** · 11-04-2009, 11:51 AM

This is exactly the same problem I'm having, although I have no idea what format switches are (I use the mail merge wizard for mail merges), and unfortunately my Excel Help is broken at the moment and won't be reinstalled until next week.

I did manage to get a workaround to work by putting the ' in front of all the data, but this is OK if there's not much data...but I've got a much bigger spreadsheet now and can't work out how to put the ' automatically in a whole column.