mail merge turns zip + 4's into zeros

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.

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

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

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 ?

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

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

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

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

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

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

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

Thanks for the grade!

Thank you for your attention to detail!