format 9 digit zip code when doing mail merge
I am doing a mail merge using an excel document as a database. When I merge the excel file into the word doc the 9 digit zip code (which has a hyphen in it in the excel file) loses the hyphen. How can I make the hyphen appear in the merged document? I've seen some posts about adding an extra column with a formula in it and this seems to be the right track but I can't find a formula that works for my specific situation. Thanks,
Karen
Karen
What version of Word are you using?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm using Office 2003 Professional
I went to Tools >Options > View > show field codes and changed the line with the zip code in it to this:
>><<_City_>><<,_State_>><<
but now I don't have any zip codes in my merged documents. Am I doing something wrong with the syntax?
Thanks, Karen
I went to Tools >Options > View > show field codes and changed the line with the zip code in it to this:
>><<_City_>><<,_State_>><<
but now I don't have any zip codes in my merged documents. Am I doing something wrong with the syntax?
Thanks, Karen
Karen, you shouldn't have to make changes in your merge file using Office 2003.
How is the zip code field formatted in the data file?
How is the zip code field formatted in the data file?
Karen: The field code switches are needed in the merge document and act on the incoming data. Examine the example below to see how how to do it.
Data file contents:
who amt zip
Ann 100 123456789
Bob 1.23 987654321
Cory 342 30385
Merge file showing the relevant field codes (toggle view with Alt-F9):
Send { MERGEFIELD "amt" \# "$0.00" } to { MERGEFIELD "who" } (SIN# { MERGEFIELD "zip" \# "000–000–000" })
Resulting file after merge:
Send $100.00 to Ann (SIN# 123–456–789)
Send $1.23 to Bob (SIN# 987–654–321)
Send $342.00 to Cory (SIN# 000–030–385)
In this example, I used a picture format to make the "amt" data display in dollars, and the "zip" data display as discussed above. Note that the picture for the zip will pad the last field with leading zeroes.
Data file contents:
who amt zip
Ann 100 123456789
Bob 1.23 987654321
Cory 342 30385
Merge file showing the relevant field codes (toggle view with Alt-F9):
Send { MERGEFIELD "amt" \# "$0.00" } to { MERGEFIELD "who" } (SIN# { MERGEFIELD "zip" \# "000–000–000" })
Resulting file after merge:
Send $100.00 to Ann (SIN# 123–456–789)
Send $1.23 to Bob (SIN# 987–654–321)
Send $342.00 to Cory (SIN# 000–030–385)
In this example, I used a picture format to make the "amt" data display in dollars, and the "zip" data display as discussed above. Note that the picture for the zip will pad the last field with leading zeroes.
ASKER
Eric,
It works! I took your first formula and inserted it with the mail merge toolbar - which is problably what you intended. I had been using the mail merge wizard and so didn't know where to put the code. That's why I put it in the funky place I put it at first.
Thanks a million,
Karen
It works! I took your first formula and inserted it with the mail merge toolbar - which is problably what you intended. I had been using the mail merge wizard and so didn't know where to put the code. That's why I put it in the funky place I put it at first.
Thanks a million,
Karen
Glad to help Karen. Word's wizards can be useful, but I find they tend to hide what you eventually will need to know. As you've apparently now done, sometimes it is best to let them do their thing, then use things like the Alt-F9 and Ctrl-* toggles to "peek under the hood" to see what really happened!