Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
DP DP is offline
external usenet poster
 
Posts: 162
Default leading zeros in mail merge

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Peter is offline
external usenet poster
 
Posts: 111
Default leading zeros in mail merge

I'm not an expert but i saw a while back a web site that dealt thoroughly
with just this question. I think the author was Graham Mayor. Someone here
will be able to give the link, or you could google his name plus "word format
switches".

Peter.

"dp" wrote:

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Cappy Cappy is offline
external usenet poster
 
Posts: 6
Default leading zeros in mail merge



"dp" wrote:

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Cappy Cappy is offline
external usenet poster
 
Posts: 6
Default leading zeros in mail merge

What you can do is select the cells with the zip codes in your database,
right click the mouse for a menu, select format cell, on the top tab select
number, then select special, then select zip code. This will format all 5
characters, even if they have leading zeros...

"dp" wrote:

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Peter is offline
external usenet poster
 
Posts: 111
Default leading zeros in mail merge

Found the link...

http://www.gmayor.com/formatting_word_fields.htm

"dp" wrote:

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA



  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default leading zeros in mail merge

Hi dp,

The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect
formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the
5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"dp" wrote in message ...
Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA


  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
DP DP is offline
external usenet poster
 
Posts: 162
Default leading zeros in mail merge

Thanks for taking time to reply. We did get this far with excel but after
the mail merge into Word labels, the zero dropped again.

"cappy" wrote:

What you can do is select the cells with the zip codes in your database,
right click the mouse for a menu, select format cell, on the top tab select
number, then select special, then select zip code. This will format all 5
characters, even if they have leading zeros...

"dp" wrote:

Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA

  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
DP DP is offline
external usenet poster
 
Posts: 162
Default leading zeros in mail merge

Peter - I printed your answer
and Macropod - I've printed out the link you sent.
Thanks for the information we have to try these.

"macropod" wrote:

Hi dp,

The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect
formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the
5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"dp" wrote in message ...
Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA


.

  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
DP DP is offline
external usenet poster
 
Posts: 162
Default leading zeros in mail merge

Peter - found the zip info in gmayor link and looks sooo simple but I managed
to unsimplify it. I used the insert merge field button for the zip and tried
to edit that, then I was adding spaces where none were needed. Took Macropods
advise about Ctrl+f9 but this was adding another set of brackets. I either
got nothing or an invalid merge field message.

Finally I typed the info {MERGEFIELD ZIP\#"00000"} and used the Ctrl+f9 and
it worked! Thank you both



"dp" wrote:

Peter - I printed your answer
and Macropod - I've printed out the link you sent.
Thanks for the information we have to try these.

"macropod" wrote:

Hi dp,

The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect
formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the
5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"dp" wrote in message ...
Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the leading
zeros are dropped.

What need to be done?

TIA


.

  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default leading zeros in mail merge

If you use Alt+F9 after inserting the merge field, the display of field
codes will be toggle on to reveal { MERGEFIELD "fieldname" } and then you
can add the formatting switch inside the closing }

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"dp" wrote in message
...
Peter - found the zip info in gmayor link and looks sooo simple but I
managed
to unsimplify it. I used the insert merge field button for the zip and
tried
to edit that, then I was adding spaces where none were needed. Took
Macropods
advise about Ctrl+f9 but this was adding another set of brackets. I
either
got nothing or an invalid merge field message.

Finally I typed the info {MERGEFIELD ZIP\#"00000"} and used the Ctrl+f9
and
it worked! Thank you both



"dp" wrote:

Peter - I printed your answer
and Macropod - I've printed out the link you sent.
Thanks for the information we have to try these.

"macropod" wrote:

Hi dp,

The following field code correctly formats 5-digit and 5+4-digit Zip
Code data, even where the source data may have incorrect
formatting such as a hyphen followed by trailing 0s after plain 5-digit
Zip Codes (eg because a db only accepts Zip Codes in the
5+4-digit format) or is formatted as a 5-digit or 9-digit string
without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: The field brace pairs (ie '{ }') for the above example are
created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"dp" wrote in message
...
Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip
column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the
leading
zeros are dropped.

What need to be done?

TIA

.



  #11   Report Post  
Posted to microsoft.public.word.mailmerge.fields
DP DP is offline
external usenet poster
 
Posts: 162
Default leading zeros in mail merge

Thanks

"Doug Robbins - Word MVP" wrote:

If you use Alt+F9 after inserting the merge field, the display of field
codes will be toggle on to reveal { MERGEFIELD "fieldname" } and then you
can add the formatting switch inside the closing }

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"dp" wrote in message
...
Peter - found the zip info in gmayor link and looks sooo simple but I
managed
to unsimplify it. I used the insert merge field button for the zip and
tried
to edit that, then I was adding spaces where none were needed. Took
Macropods
advise about Ctrl+f9 but this was adding another set of brackets. I
either
got nothing or an invalid merge field message.

Finally I typed the info {MERGEFIELD ZIP\#"00000"} and used the Ctrl+f9
and
it worked! Thank you both



"dp" wrote:

Peter - I printed your answer
and Macropod - I've printed out the link you sent.
Thanks for the information we have to try these.

"macropod" wrote:

Hi dp,

The following field code correctly formats 5-digit and 5+4-digit Zip
Code data, even where the source data may have incorrect
formatting such as a hyphen followed by trailing 0s after plain 5-digit
Zip Codes (eg because a db only accepts Zip Codes in the
5+4-digit format) or is formatted as a 5-digit or 9-digit string
without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE
{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: The field brace pairs (ie '{ }') for the above example are
created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[Microsoft MVP - Word]


"dp" wrote in message
...
Using 2007 Excel and 2007 Word
A co-worker has a name/addr file in Excel and formatted the zip
column as
category, special 00000. Zip shows fine.

However when uses word mail merge to produce mailing labels, the
leading
zeros are dropped.

What need to be done?

TIA

.

.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why do zip codes w/ leading zeros disappear when doing mail merge Dory Mailmerge 1 December 17th 08 08:18 PM
how do i display leading zeros when mail merge to Word doc relo Mailmerge 1 June 16th 07 03:57 AM
Mail Merge drops leading zeros from Access text field zips? LisaLynnGuy Mailmerge 1 August 25th 05 06:59 PM
dropping leading zeros in mail merge Jim Mailmerge 3 June 17th 05 07:02 AM
Mail Merge Dropping leading zeros richard via OfficeKB.com Mailmerge 1 February 4th 05 11:58 PM


All times are GMT +1. The time now is 09:06 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"