Hello John,
(Not sure you are the same John who has been posting over the past few
days...)
A couple of thoughts on this...
a. it's entirely possible that the conclusions I came to when I wrote
http://tips.pjmsn.me.uk/t0003.htm needs are only part of the story
and that there are yet more wrinkles in the way that the OLE DB provider
treats Excel data on its way to Word.
b. However, Excel does make decisions about the "content type" of
cells at the time you enter the content. Changing the cell formatting
later does not necessarily alter Excel's view of what you originally
entered. That's why the more radical solution described in the section
titled
"1. Format a column as text via Data|Text to Columns..."
is sometime needed. However, it may not be the right solution for all
types of data.
Peter Jamieson
http://tips.pjmsn.me.uk
John wrote:
I have a strange thing happening with telephone number formatting when
merging and need help figuring it out.
The problem is that new records when added to the Excel spreadsheet
show/print differently than older records in the spreadsheet. Old records
print with correct formatting of (913) 777-4201 new records show formatting
as exactly (000) 000-0000. Worse yet, two records out of ten of new records
entered print with correct formatting of (913) 777-4201. All records have
same and consistent formatting in Excel
I am using Excel 2007 with merge forms docs in Word 2007.
In Excel spreadsheet I have several columns of telephone numbers. Columns
are all formatted as numbers and typed in each cell as continuous numbers
1234567780 instead of (402) 555-0509.
There are 500+ records/rows of which the top 9 rows are dummy entries.
OLEDB is the merge choice.
New records are entered, altered and old deleted daily so the total number
of records remains under 600 rows. There are less than 40 columns total to
the spreadsheet.
I have tried the following codes in Word doc to get the phone numbers to
format as
(123) 223-5926:
{MERGEFIELD EmployerPhone \# "(###) ###'-'####}
this one produces correct old records but ( ) - for new records
{MERGEFIELD EmployerPhone \# "(000) 000'-'0000} {IF{MERGEFIELD
EmployerPhone} "" " "}
This on produces exactly (000) 000-0000 for all new records and fine for old
records
{MERGEFIELD EmployerPhone \# "(000) 000'-'0000} This one produces correct
numbers and format for old records and blank or (000) 000-0000 for new ones
Our spreadsheet is password protected and read-only for all others accessing
the Word Docs from the server directory. Two separate work stations have
full access/permission to add, change and delete records from the Excel
spreadsheet.
Please help me understand what is going wrong and fix this problem. Do I
need to reformat all my Excel columns that contain telephone numbers or
change the Word Merge formatting or both?