Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

format 9 digit zip code when doing mail merge

Posted on 2006-05-15
7
Medium Priority
?
1,051 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:ksilvoso
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Etircet
ID: 16685957
What version of Word are you using?
0
 
LVL 21

Accepted Solution

by:
Eric Fletcher earned 1000 total points
ID: 16685993
You can format your merge field with the numeric picture field. For example, if you wanted "123456789" to appear as "123-456-789" you could add this to the field code that places the zip code:
\# "000'-'000'-'000"

Note that the hyphens need to be encased in a single quote and the whole string within double quotes to work. Alternatively, you could use the en dash character by entering Ctrl-NumericKeypadHyphen or typing Alt-0150 (use the numeric keypad for the digits) instead of the '-' combination. This would give you a slightly longer dash than a hyphen ("–" versus "-") — which would be the preference of typesetters if that mattered at all to you!
0
 
LVL 1

Author Comment

by:ksilvoso
ID: 16691610
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_>><<_Postal_\# "00000'-'0000”>>

but now I don't have any zip codes in my merged documents.  Am I doing something wrong with the syntax?
Thanks, Karen
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:Etircet
ID: 16691700
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?
0
 
LVL 21

Expert Comment

by:Eric Fletcher
ID: 16693296
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.
0
 
LVL 1

Author Comment

by:ksilvoso
ID: 16694508
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
0
 
LVL 21

Expert Comment

by:Eric Fletcher
ID: 16694785
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!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Suggested Courses

580 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