Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Leslie Leslie is offline
external usenet poster
 
Posts: 37
Default How to format Zip Code from 9 to 5

I hope this makes sense! We have a glitch in our computer software that
automatically places zeros in the place of the zip code plus 4 numbers when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000. Since we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard 5
numbers.

I have tried different formatting using Bookmarks, and this works if there
are only zeros in the plus 4 field, but if there ARE numbers what I get is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and only
show the first five?
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP on news.microsoft.com Doug Robbins - Word MVP on news.microsoft.com is offline
external usenet poster
 
Posts: 407
Default How to format Zip Code from 9 to 5

As I do not think that it is possible to overcome this will formatting
switches, you might have merge to a new document letting the merge insert
the 75043-0000 or 75043--000 or 75043-000 and then use a Wildcard Replace
where you search for [-]{1,}[0]{3,} and leave the Replace with control
empty.

That will replace each of the 75043-0000 or 75043--000 or 75043-000 with
75043

--
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

"Leslie" wrote in message
...
I hope this makes sense! We have a glitch in our computer software that
automatically places zeros in the place of the zip code plus 4 numbers
when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000. Since
we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard 5
numbers.

I have tried different formatting using Bookmarks, and this works if there
are only zeros in the plus 4 field, but if there ARE numbers what I get is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and
only
show the first five?



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How to format Zip Code from 9 to 5

Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be returned as 75043.

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
[MVP - Microsoft Word]


"Leslie" wrote in message ...
I hope this makes sense! We have a glitch in our computer software that
automatically places zeros in the place of the zip code plus 4 numbers when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000. Since we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard 5
numbers.

I have tried different formatting using Bookmarks, and this works if there
are only zeros in the plus 4 field, but if there ARE numbers what I get is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and only
show the first five?


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP on news.microsoft.com Doug Robbins - Word MVP on news.microsoft.com is offline
external usenet poster
 
Posts: 407
Default How to format Zip Code from 9 to 5

I should have known that you would come up with a way of doing it.

--
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

"macropod" wrote in message
...
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be
returned as 75043.

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
[MVP - Microsoft Word]


"Leslie" wrote in message
...
I hope this makes sense! We have a glitch in our computer software that
automatically places zeros in the place of the zip code plus 4 numbers
when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000. Since
we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard 5
numbers.

I have tried different formatting using Bookmarks, and this works if
there
are only zeros in the plus 4 field, but if there ARE numbers what I get
is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and
only
show the first five?




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How to format Zip Code from 9 to 5

Hi Doug,

Your mission, if you choose to accept it, is to figure out how the code works ...

--
Cheers
macropod
[MVP - Microsoft Word]


"Doug Robbins - Word MVP on news.microsoft.com" wrote in message
...
I should have known that you would come up with a way of doing it.

--
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

"macropod" wrote in message ...
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be returned as 75043.

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
[MVP - Microsoft Word]


"Leslie" wrote in message ...
I hope this makes sense! We have a glitch in our computer software that
automatically places zeros in the place of the zip code plus 4 numbers when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000. Since we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard 5
numbers.

I have tried different formatting using Bookmarks, and this works if there
are only zeros in the plus 4 field, but if there ARE numbers what I get is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and only
show the first five?







  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP on news.microsoft.com Doug Robbins - Word MVP on news.microsoft.com is offline
external usenet poster
 
Posts: 407
Default How to format Zip Code from 9 to 5

The {SET ID {MERGEFIELD ZipCode} sets the zip code as the text of a bookmark
with the name of ID
-{ ID } evaluates to -99999-9999 whereas -ID is treated mathematically and
evaluates to -99999+9999
so =-{ID}-ID evaluates to -99999-9999-99999+9999 or -99999-99999
so what is actually happening is that the digits in the zip code after the -
are being stripped off and what is left is a negative number that is twice
the first five digits of the zip code
Then the {=-{=-{ID}-ID}/2} changes the sign and divides the number by 2 so
that it is just the first five digits of the zip code.
The {SET EXP1{=-{=-{ID}-ID}/2}} assigns that to the bookmark EXP1, which is
then displayed by the {QUOTE EXP1 }

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

"macropod" wrote in message
...
Hi Doug,

Your mission, if you choose to accept it, is to figure out how the code
works ...

--
Cheers
macropod
[MVP - Microsoft Word]


"Doug Robbins - Word MVP on news.microsoft.com"
wrote in message ...
I should have known that you would come up with a way of doing it.

--
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

"macropod" wrote in message
...
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be
returned as 75043.

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
[MVP - Microsoft Word]


"Leslie" wrote in message
...
I hope this makes sense! We have a glitch in our computer software
that
automatically places zeros in the place of the zip code plus 4 numbers
when
there are no numbers there, 75043-0000 or 75043--000 or 75043-000.
Since we
do not have control over programming we need to overcome this problem,
hopefully, by using MS Word. We are okay with not seeing the plus four
numbers if they are there, because most often we only get the standard
5
numbers.

I have tried different formatting using Bookmarks, and this works if
there
are only zeros in the plus 4 field, but if there ARE numbers what I get
is
Word subtracting the plus four and creating a whole new zip code.

{IF {ZIP} 99999 "{ ZIP \# "00000'-'0000"}""{ZIP \# "00000"}"}
takes this number 75841-1111 and makes it 74730.

Is there any way to get Word to just truncate the last four numbers and
only
show the first five?






  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default How to format Zip Code from 9 to 5

FWIW previously I had

{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }

which deals with numeric ZIPs up to 9 digits and 12345-6789 text format
zips as long as the latter were already correctly formatted for output,
which I think (being text) they typically would be, i.e. it would not
deal with 2345-6789 meaning 02345-6789

With a slightly modified test any "-zero" suffix - as mentioned - e.g.
-0000, --000 etc. (but not a single trailing "-") can also be removed
and just as an alternative approach to the output you could do it as
something like.

{QUOTE{SET Z{MERGEFIELD zip}}{={Z}-{Z}} \#";'{Z}';'{={Z}-99999.5 \#"'{=Z
\#00000'-'0000}';'{=Z \#00000}'"}

I guess there could be a simplification in there to split the three
cases ("hyphenated", "5 digits or less" and "6-9 digits" using a single \#.

Either way, I think something like that should deal with the vast
majority of these ZIP issues except the case where the ODBC provider/OLE
DB provider has already converted texts to 0 because of mixed types in
the Zip column.

Cheers,

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be
returned as 75043.

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.

  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How to format Zip Code from 9 to 5

Hi Peter,

How about:
{SET Val {MERGEFIELD ZipCode}}{IF{Val} 99999 {SET ID {Val \# "00000'-'0000"}} {SET ID {Val}}}{=-{=-{ID}-ID}/2 \#
0000;;}{=-({ID}*(-1)-ID)/2 \# ;-0000;}
This handles 4-digit, 5-digit, 5+4-digit formatted, 5+4-digit unformatted (ie 9 digit) and the OP's scenario. It works on the
assumptions that no:
.. 5+4-digit zip codes have '0000' as the 4-digit part; and
.. 5-digit zip codes start with 0.

--
Cheers
macropod
[MVP - Microsoft Word]


"Peter Jamieson" wrote in message ...
FWIW previously I had

{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }

which deals with numeric ZIPs up to 9 digits and 12345-6789 text format zips as long as the latter were already correctly
formatted for output, which I think (being text) they typically would be, i.e. it would not deal with 2345-6789 meaning 02345-6789

With a slightly modified test any "-zero" suffix - as mentioned - e.g. -0000, --000 etc. (but not a single trailing "-") can also
be removed and just as an alternative approach to the output you could do it as something like.

{QUOTE{SET Z{MERGEFIELD zip}}{={Z}-{Z}} \#";'{Z}';'{={Z}-99999.5 \#"'{=Z \#00000'-'0000}';'{=Z \#00000}'"}

I guess there could be a simplification in there to split the three cases ("hyphenated", "5 digits or less" and "6-9 digits" using
a single \#.

Either way, I think something like that should deal with the vast majority of these ZIP issues except the case where the ODBC
provider/OLE DB provider has already converted texts to 0 because of mixed types in the Zip column.

Cheers,

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Leslie,

You can eliminate the unwanted portions via a field coded as:
{QUOTE{SET ID {MERGEFIELD ZipCode}}{SET EXP1{=-{=-{ID}-ID}/2}}{EXP1}}
Where 'ZipCode' is the name of the data field containing your Zip Codes.

With this coding, any of 75043-0000 or 75043--000 or 75043-000 will be returned as 75043.

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.


  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default How to format Zip Code from 9 to 5

Hi macropod,

Just as a quick response: it all works as described but...

...no:
. 5-digit zip codes start with 0.


this is not the case, at least as far as US ZIPs are concerned (which is
why people often have to use the '00000' format with 5-digit ZIPs in
Excel). I think that can be easily fixed by changing your 0000 to a
00000, but the lack of 4-digit ZIPs there may allow further
simplification in your code. I don't have a full list of the ZIP rules
but found http://en.wikipedia.org/wiki/ZIP_code useful.

...no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and


I have also made this assumption (which seems to be in line with what
the OP is trying to do anyway). The wikipedia article doesn't explicitly
state that -0000 is not used but I hope the USPS managed to avoid it :-)

For an Excel column that supported international postcodes things would
have to be rather different anyway. If you tried to put all types of
code in a single column, it would have to be alpha (because of e.g. UK
and Canadian codes) and then you probably would have to deal with
4-digit codes, unless it's OK in countries with 4-digit codes to preface
the code by 0 etc. etc. Then you would have to deal with what the OLE DB
provider does to things it thinks are numbers. At which point it's
probably simpler from a mailmerge perspective to suggest that people put
the entire address in a single Excel cell...

I'll have another look later anyway.

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Peter,

How about:
{SET Val {MERGEFIELD ZipCode}}{IF{Val} 99999 {SET ID {Val \#
"00000'-'0000"}} {SET ID {Val}}}{=-{=-{ID}-ID}/2 \#
0000;;}{=-({ID}*(-1)-ID)/2 \# ;-0000;}
This handles 4-digit, 5-digit, 5+4-digit formatted, 5+4-digit
unformatted (ie 9 digit) and the OP's scenario. It works on the
assumptions that no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and
. 5-digit zip codes start with 0.

  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default How to format Zip Code from 9 to 5

OK, IMO yours is the best approach so far. AFAICS you can modify it ever
so slightly to

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2
\#;-0000;}

which still does everything you say but always does the first five
digits as 5 digits and drops the second part of the IF

Then if you think you need to display "-0000" you can do

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2
\#;-0000;'-0000'}

or

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000'-0000';;}{=-({Val}*(-1)-Val)/2 \#;-0000;'-0000'}

depending on the requirement.

/If/ you need also to deal with zips with a trailing "-" but no digits
after it, you can either insert an extra test, e.g.

{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val { ={ Val }-0
}}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}
or
{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val {QUOTE
"{Val}0"}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2
\#;-0000;}

or nest the 99999 test inside the new IF.

Best regards

Peter Jamieson

http://tips.pjmsn.me.uk

Peter Jamieson wrote:
Hi macropod,

Just as a quick response: it all works as described but...

...no:
. 5-digit zip codes start with 0.


this is not the case, at least as far as US ZIPs are concerned (which is
why people often have to use the '00000' format with 5-digit ZIPs in
Excel). I think that can be easily fixed by changing your 0000 to a
00000, but the lack of 4-digit ZIPs there may allow further
simplification in your code. I don't have a full list of the ZIP rules
but found http://en.wikipedia.org/wiki/ZIP_code useful.

...no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and


I have also made this assumption (which seems to be in line with what
the OP is trying to do anyway). The wikipedia article doesn't explicitly
state that -0000 is not used but I hope the USPS managed to avoid it :-)

For an Excel column that supported international postcodes things would
have to be rather different anyway. If you tried to put all types of
code in a single column, it would have to be alpha (because of e.g. UK
and Canadian codes) and then you probably would have to deal with
4-digit codes, unless it's OK in countries with 4-digit codes to preface
the code by 0 etc. etc. Then you would have to deal with what the OLE DB
provider does to things it thinks are numbers. At which point it's
probably simpler from a mailmerge perspective to suggest that people put
the entire address in a single Excel cell...

I'll have another look later anyway.

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Peter,

How about:
{SET Val {MERGEFIELD ZipCode}}{IF{Val} 99999 {SET ID {Val \#
"00000'-'0000"}} {SET ID {Val}}}{=-{=-{ID}-ID}/2 \#
0000;;}{=-({ID}*(-1)-ID)/2 \# ;-0000;}
This handles 4-digit, 5-digit, 5+4-digit formatted, 5+4-digit
unformatted (ie 9 digit) and the OP's scenario. It works on the
assumptions that no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and
. 5-digit zip codes start with 0.



  #11   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default How to format Zip Code from 9 to 5

Hi Peter,

I think you've got too much free time on your hands ...

I agree that adding the extra 0 to preserve leading 0s in the 5-digit part of the Zip Codes is necessary, and I follow what you've
done by dropping the 'SET ID' part of the field coding and substituting Val expressions - it reduces the overal field coding effort
and creates one less bookmark for people to worry about.

I had a look on the USPS website and it seemed from what I read there that a +4 code of 0000 wouldn't occur, but it'd be nice if
someone could confirm this. If they do occur, I can't see how a mailmerge using data like the OP's could differentiate between a
valid 0000 and an invalid one.

--
Cheers
macropod
[MVP - Microsoft Word]


"Peter Jamieson" wrote in message ...
OK, IMO yours is the best approach so far. AFAICS you can modify it ever so slightly to

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}

which still does everything you say but always does the first five digits as 5 digits and drops the second part of the IF

Then if you think you need to display "-0000" you can do

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;'-0000'}

or

{SET Val {MERGEFIELD ZipCode}}{IF {Val} 99999 {SET Val {Val \#"00000'-'0000"}}}{=-{=-{Val}-Val}/2
\#00000'-0000';;}{=-({Val}*(-1)-Val)/2 \#;-0000;'-0000'}

depending on the requirement.

/If/ you need also to deal with zips with a trailing "-" but no digits after it, you can either insert an extra test, e.g.

{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val { ={ Val }-0 }}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}
or
{SET Val {MERGEFIELD ZipCode}}{IF {Val} = "*-" {SET Val {QUOTE "{Val}0"}}{IF {Val} 99999 {SET Val {Val
\#"00000'-'0000"}}}{=-{=-{Val}-Val}/2 \#00000;;}{=-({Val}*(-1)-Val)/2 \#;-0000;}

or nest the 99999 test inside the new IF.

Best regards

Peter Jamieson

http://tips.pjmsn.me.uk

Peter Jamieson wrote:
Hi macropod,

Just as a quick response: it all works as described but...

...no: . 5-digit zip codes start with 0.


this is not the case, at least as far as US ZIPs are concerned (which is why people often have to use the '00000' format with
5-digit ZIPs in Excel). I think that can be easily fixed by changing your 0000 to a 00000, but the lack of 4-digit ZIPs there may
allow further simplification in your code. I don't have a full list of the ZIP rules but found
http://en.wikipedia.org/wiki/ZIP_code useful.

...no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and


I have also made this assumption (which seems to be in line with what the OP is trying to do anyway). The wikipedia article
doesn't explicitly state that -0000 is not used but I hope the USPS managed to avoid it :-)

For an Excel column that supported international postcodes things would have to be rather different anyway. If you tried to put
all types of code in a single column, it would have to be alpha (because of e.g. UK and Canadian codes) and then you probably
would have to deal with 4-digit codes, unless it's OK in countries with 4-digit codes to preface the code by 0 etc. etc. Then you
would have to deal with what the OLE DB provider does to things it thinks are numbers. At which point it's probably simpler from
a mailmerge perspective to suggest that people put the entire address in a single Excel cell...

I'll have another look later anyway.

Peter Jamieson

http://tips.pjmsn.me.uk

macropod wrote:
Hi Peter,

How about:
{SET Val {MERGEFIELD ZipCode}}{IF{Val} 99999 {SET ID {Val \# "00000'-'0000"}} {SET ID {Val}}}{=-{=-{ID}-ID}/2 \#
0000;;}{=-({ID}*(-1)-ID)/2 \# ;-0000;}
This handles 4-digit, 5-digit, 5+4-digit formatted, 5+4-digit unformatted (ie 9 digit) and the OP's scenario. It works on the
assumptions that no:
. 5+4-digit zip codes have '0000' as the 4-digit part; and
. 5-digit zip codes start with 0.


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
My zip code format is lost when merging from Excel to Word... Roy Laws Microsoft Word Help 1 May 26th 21 09:31 PM
My zip code format is lost when merging from Excel to Word... Merging Zip Code Errors Microsoft Word Help 12 October 20th 09 09:46 PM
My zip code format is lost when merging from Excel to Word... MaggieDonuts Microsoft Word Help 2 June 5th 09 12:13 AM
Can I format zip code merge field to add hyphen if zip plus 4? ShaniraW Mailmerge 1 March 7th 06 08:36 PM
Mail Merge Zip Code will not format correctly ba Mailmerge 1 December 8th 04 03:15 AM


All times are GMT +1. The time now is 07:46 PM.

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"