Solved

mail merge turns zip + 4's into zeros

Posted on 2010-08-31
14
776 Views
Last Modified: 2012-05-10
I have a user that is doing a mail merge on our 2003 windows terminal servers. We are using Office 2003 SP3 and the terminal server OS is windows 2003 enterprise R2.

This user wants to do a mail merge in word using an excel file. This file has both five digit and nine digit zip codes. For some reason, when the merge is done, all of the nine digit zip codes show up as "o."

I have tried changing the cell format to text, numbers, special (zip code), and a couple of others with no luck.

I found this article and tried both solutions and neither worked for me.

http://support.microsoft.com/kb/320473

Any help is appreciate.

Thanks,

Justin
0
Comment
Question by:JustinGSEIWI
  • 6
  • 6
  • 2
14 Comments
 
LVL 25

Expert Comment

by:Ron M
ID: 33573292
Try this...

Copy the entire zip+4 Column...
Open ms Notepad, ...Paste !
In Notepad, select All, Copy again...

Now go back to Excel.  Insert a NEW column right next to the zip+4 column.....
Format the column as text.
Go to the first cell at the top.... .  Paste !

Rename the NEW column to the name of the OLD zip column.... rename or delete the old column.


...Let me know how that works for you.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33573297
Hello,

have you tried a numeric formatting in Word? In your merge document, hit Alt-F9 to show the fields and then add the following switch to your zipcode field

{ MERGEFIELD "zipcode" \# 00000'-'0000 }

Works a beauty in my tests. See attached documents for source and target. The zip code is formatted in Excel with the Special - Zip Code + 4, but it also works if it's formatted as text.

cheers, teylyn

mergesource.xls
mergetarget.doc
0
 

Author Comment

by:JustinGSEIWI
ID: 33577333
xuserx2000,

This worked in my test. Why did it work? Doing this every time is not ideal.

Teylyn,

I tried your solution but when I hit alt-F9, nothing happens. I am logged into a terminal server, maybe that is why?

I also tried right clicking the column and hitting format cells and then selected custom and typed in { MERGEFIELD "zipcode" \# 00000'-'0000 } but word said it could not use it.

Thanks,

Justin
0
 
LVL 25

Expert Comment

by:Ron M
ID: 33580195
It works because....when you copy the DISPLAYED value....  ex. 33901-0210... .and paste into notepad.... then copy from notepad... you are copying the displayed value to an ACTUAL value...and pasting the entire pre-formatted value into a TEXT column, where no more formatting or evaluation is going to occur.  When you do a mail merge...it merges the ACTUAL value...not what is displayed by formatting cells in excel.

So for example...
If I had a column that was ...Phone Number... and the actual value of a cell was 5555555555.... .that is how it will come out on the mail merge....however... I could use a custom format... to make it 555-555-5555....in the display...that doesn't change the actual value.



Am I making sense here ?
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33581567
Justin,

if Alt-F9 does not work for your, right-click the field name and select "Toggle Field Codes" to display the field code. Here you can enter the formatting switch into the field.

see the screenshot. After that, you need to toggle field codes off again. And don't forget to update the field after you've made a change (right-click the field and click Update Field)

cheers, teylyn
field-codes.gif
0
 

Author Comment

by:JustinGSEIWI
ID: 33619147
xuserx,

That makes sense. Thanks for the work around. This will work for now, but I still need a full solution.

teylyn,

I cannot find toggle field codes anywhere. What do you mean by field name? I right clicked in a few places and checked the menus, but didn't find it. I am running office 2003 if makes a difference?

I am checking with support to see if we can correct this issue by having the data in the Excel file formatted differently. I am waiting on this right now. In the mean time, i am happy to try any other suggested fixes.

Thanks,

Justin
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33622230
Justin,

The screenshot I posted has been taken with Word 2003. When you set up your mail merge document, you must enter the mail merge fields. In normal view they look something like

<<firstname>> <<lastname>>

Can you see these in your document?

These are merge fields. Yours may look different, because you may use different names. Field names. The field names will be the column headers in your Excel source file. Right-click on one of the fields and you will see the context menu as in the screenshot above.

There is a setting in Word to highlight fields. Use Tools - Options - View tab. in the Field shading drop-down select "Always". That will give all the fields in your merge document a dark gray background, so you can see them more easily.

You can also use Tools - Options - View tab - tick Field codes.

This will show the fields expanded to their field codes, so they look like

{ MERGEFIELD "firstname" } { MERGEFIELD "lastname" }

You can click inside the curly braces to add the formatting switch as I have outlined above.

{ MERGEFIELD "zipcode" \# 00000'-'0000 }

In this example, "zipcode" is the field name. Again, your field name may be different, depending on your column header in your source data.

If you don't see any fields, but rather the merged information, go back in the Mail Merge Wizard. In Step 4 of the wizard you definitely should see the field names in the <<firstname>> format.

If that does not help, I'm beginning to wonder if you are looking at a mail merge source document at all. See the two files I attached above. Save them to your hard drive. Open the Word document, start the Mail Merge Wizard. In step 3 navigate to the Excel file and select it as the source. Click to Step 4. Do you see the field names there? Right click a field to see the context menu. What do you see? Switch on the field codes with any of the methods mentioned above. What do you see?

cheers, teylyn

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:JustinGSEIWI
ID: 33630820
teylyn,

I attached what I see when i right click the header field. I also checked in tools - options - view tab and could not find any of the options you suggested.

I am running SP3 of office 2003. Are you on SP3?

I downloaded your Excel file that you posted above and right clicked on area C1, which is titled zip and did not see toggle field codes either.

Thanks,

Justin
Capture1.PNG
0
 

Author Comment

by:JustinGSEIWI
ID: 33631105
Ignore that last update. I was right clicking in the Excel document. I was confused. I opened Word and start going through the merge. I select labels in step one, then I click label options in step 2 and under product number I select 5160 - address. I click browse in step 3 and select my excel file. Once I do that, I get the mail merge recipients box and see everybody listed. At this point, I see all the five digit zip codes but all of the zip codes that are nine digit are turned to 0. Once I click OK, I am left with a screen that looks like what I attached.

On this screen now I can hit alt-f9 and and all the labels say {next}. I can also right click and toggle field codes. Now I am not sure what to do at this point. Where do I enter { MERGEFIELD "zipcode" \# 00000'-'0000 } ?

Thanks,

Justin
Capture1.PNG
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33632455
Justin,

I thought a merge document already existed. You say in your question:

"This user wants to do a mail merge in word using an excel file. This file has both five digit and nine digit zip codes. For some reason, when the merge is done, all of the nine digit zip codes show up as "o."

You need to edit this user's merge document the way I described above.

Or, in the document from your latest screen shot, you need to start adding the actual fields that you want to see on the labels. Click "Next: Arrange your labels" in the right hand mail merge pane to go to step 4, where you can arrange the fields in the first label.

I fear that it is a bit beyond the scope of this question to teach you how to use mail merge.

You mention that there are 5 and 9 digit zip codes in the user's data. Can you attach a small sample, with anonymous data in an Excel file?

cheers, teylyn
0
 

Author Comment

by:JustinGSEIWI
ID: 33636890
Teylyn,

Sorry for the confusion. I have a user with an excel document. The user opens up word, no word document is involved, it is blank. Then she starts the mail merge and selects the Excel file.

So would the excel document be considered the users merge document?

I do not know how to do a full mail merge. I only know how to reproduce the problem the use is having to troubleshoot.

I attached a faile with fake information in it. It is the same file I am using, I just replaced the data with fake data.

Basically, I can reproduct the problem by clicking browse in step 3 and then selecting the Excel file attached to this message. Then I get the mail merge recipients box and I will see all the nine digit zip codes turn to 0's.

Now this is werid. Before I post this I decided to test the fake file I just attached. All the nine digit zip codes are fine in the mail merge. Does that mean that something was in the cells causing a probleM?

The mail merge has hundreds in it. I only have six examples in this excel file.

Thanks,

Justin
test.xls
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 33641486
Justin,

thanks for the data sample. That really helps with things.

The way I see it, it might be better to ensure in Excel that the zip codes are in the correct format. It's way easier to check a value and apply formatting based on the properties of the value than to do the same thing in Word.

Since you have a mix of 5 digit and 5+4 zip codes, a blanket format switch in Word will not work. Letting word figure out what the length of the zip code is and applying a different switch for different lengths can be challenging.

Also, the Excel file may contain zip codes that are actually text. For example, the first three rows of your example have numbers as the zip code, but the last three are text, because of the - sign before the last 4 digits.

Now, depending on the quality of your data, you may actually have a mix with three types of items involved:
- text zip codes
- numbers with 5 digits (or less)
- numbers with 9 digits (or less)

The number CAN be formatted to appear correctly in Excel with the Special - zip format, but I'm not sure this would transport over to Word correctly.

If I were to do it, I'd save myself a lot of headache and create a new column in the Excel file with a formula like this:

=IF(ISNUMBER(G2),IF(LEN(G2)<=5,TEXT(G2,"00000"),TEXT(G2,"00000-0000")),G2)

Copy down.

Now, if the value in column G is text, it will be taken at face value, no manipulation. If the value is a number, we check how many digits it has. If it is five or less, then we'll turn it into a fixed length text with leading zeros, if required. If it's more than 5 digits, we'll return it as text in the 5+4 format, again with leading zeros if required.

I think this should cover all situations. In row 1 of the new column in Excel put the field name, for example merge_zip or print_zip or something like that. Save and close the Excel file.

Then use the field merge_zip (or print_zip) in step 4 of the Mail Merge Wizard in Word. Now your zip codes will appear exactly as they are in the new column in Excel. Being text, they won't lose their leading zeros.

Let me know if that works for you.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
ID: 33738645
Thanks for the grade!
0
 

Author Comment

by:JustinGSEIWI
ID: 33738657
Thank you for your attention to detail!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now