formatting Excel cells for Word mail merge

[jimoblak]

I have some numerical data that has been formatted as 'custom' to make a phone number:

(000) 000-0000

When I do a mail merge in Word, this formatting is lost. the numbers appear in the format of:

0000000000.0

How can I maintain the formatting from Excel?

 

[Dreamboat]

Version please.

Or try changing the data connection method. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[jimoblak]

Excel 97 / Word 2000

I ended up exporting a CSV file to force the formatting into a plain text format. That resolved my problem.

 

[dcompto]

The following is from Microsoft Knowledge Base Article - 212323

WD2000: Date, Currency, and Phone Number Fields Merge Incorrectly When You Use an Access or Excel Data Source
The information in this article applies to:
Microsoft Word 2000

This article was previously published under Q212323
SYMPTOMS
When you perform a mail merge using ODBC with a Microsoft Access or Microsoft Excel data source, the merge results for the Date, Phone Number, and Currency fields are incorrect. For example:

The Date field appears in the following format:
1995-10-22 00:00:00

The Currency field appears in the following format:
12.00

The Phone Number field from an Access data source appears in the following format:
-7081234567.00

CAUSE
The data appears in its native, stored format in Access or Excel. When you use DDE to convert the Access or Excel data instead of ODBC, the data is masked to display the correct format.
RESOLUTION
Method 1: Use DDE to Connect to the Access or Excel Database
Click to select the Select Method (Confirm Conversion) check box in the Open Data Source dialog box, locate and select your Access/Excel database, and then click Open.
In the Confirm Data Source dialog box, click to select Microsoft Access or Microsoft Excel Database via DDE (*.mdb), and then click OK.
If DDE is not available, please see the following article in the Microsoft Knowledge Base:
137076 WD: Can't Use Microsoft Access Files, *.MDB, or DDE with Word



Method 2: Use ODBC and Add a "Picture" Switch to the Date/Currency fields.
Include a numeric picture switch (\#) in the Currency field, so that the Currency field looks like this:
{mergefield number \# $####,0.0}

NOTE: The {mergefield number \# $####,0.0} string works for SQL Server 6 as well.
Include a numeric picture switch (\#) in the Phone Number field, so that the Phone Number field looks like this:
{mergefield phone \# ###'-'###'-'####}

NOTE: The hyphen inside the mergefield is in quotation marks. Also, the quotation marks are single quotation marks.
Include a date-time picture switch (\@) in the Date field, so that the Date field looks like this:
{mergefield date \@ "MMMM d, yyyy"}

NOTE: The picture switch is case-sensitive; also, you must use quotation marks around the picture string.