Merging Telephone numbers produces weird resulsts

[John]

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?

 

[Peter Jamieson]

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]

Thank you Peter for such a quick response. These problems are making me
crazy. The spreadsheet number columns were originally changed via the
Data/Text to Columns. Everything was working fine. Then the zip codes
started weirding out. I fixed that with your expert help, now the telephone
number columns, which were working fine previously, are no longer fine.

It is as if we have some strange loop hovering and it lands on something new
and odd periodically just for fun.

 

[Peter Jamieson]

Hello John,

It is as if we have some strange loop hovering and it lands on something new
and odd periodically just for fun.

Yes, it's easy to get that impression with software in general.

If your problem is immediate, i.e. you need to fix it right now to get
your mailing out, my best suggestion is to copy/paste your Excel data
into a Word document (if the column count doesn't exceed Word's maximum
table column count, this is more likely to be useful) and use that Word
table as a data source. At least you then have a better chance of seeing
what is going on.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[John]

Thank you again Peter for being helpful.

I decided to try the Text/Columns formatting again on all the telephone
number columns in Excel. It worked and now they are all formatting properly
in Word when merged.

I will watch to see if new records added cause any more problems. If they
do, we may have to perform the Data Text to Columns with each new record
entered in the spreadsheet.

Have an enjoyable weekend.