Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Telephone numbers produces weird resulsts
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? -- John R. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Telephone numbers produces weird resulsts
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? |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Telephone numbers produces weird resulsts
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. -- John R. "Peter Jamieson" wrote: 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? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Telephone numbers produces weird resulsts
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 wrote: 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. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging Telephone numbers produces weird resulsts
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. -- John R. "Peter Jamieson" wrote: 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 wrote: 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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switch for mail merging telephone-number field | Mailmerge | |||
Typing letters produces numbers | Microsoft Word Help | |||
mail merge field for telephone numbers; what switches? | Mailmerge | |||
format telephone numbers Mail Merge | Mailmerge | |||
How do I format telephone numbers in Word? | Mailmerge |