Zip Code separation 3

[charmor]

I need a formula to separate the 5 diget zip code from the postal extension for mailing labels (ie change 793476006 to 79347-6006. Any suggestions?

 

[kenhamady]

You can use the first line to just get the 5 digits, or all 3 to get the whole formatted zip. This assumes that the zip is stored as a string.

{zip} [1 to 5 ]

+ "-" +

{zip} [6 to 9 ]

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[lbass]

You could try this, assuming your original field is a string, not a number:

left({zipcode},5)+"-"+right({zipcode},4)

If your original field is a number, then try the following to avoid trimming leading zeros, i.e., so you don't lose the leading zero in a zip like 02453-5422:

left(if len(totext({zipcode},0,&quot;&quot) < 9 then replace(space(9-len(totext({zipcode},0,&quot;&quot)),&quot; &quot;,&quot;0&quot+totext({zipcode},0,&quot;&quot,5)+&quot;-&quot;+right(if len(totext({zipcode},0,&quot;&quot) < 9 then replace(space(9-len(totext({zipcode},0,&quot;&quot)),&quot; &quot;,&quot;0&quot+totext({zipcode},0,&quot;&quot,4)

There might be a simpler way, but this worked when I tried it.

-LB

 

[lbass]

Ken--didn't see your post, and there it is--the simpler way...

-LB

 

[lbass]

Here's a version that's simpler than my previous suggestion if your field is a number:

totext({zipcode},&quot;000000000&quot[1 to 5]+&quot;-&quot;+totext({zipcode},&quot;000000000&quot[6 to 9]

-LB

 

[dgillz]

heres a simple formula:


picture(totext(123456789,0,&quot;&quot,&quot;xxxxx-xxxx&quot


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[kenhamady]

Don, that one might not work. A New Jersey zip code that starts with a zero would be 1 digit short as a number. The dash would end up in the wrong spot.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

I'd sanity check the zip.

If len({Customer.Postal Code}) = 9 then
{Customer.Postal Code} [1 to 5]+ &quot;-&quot; +{Customer.Postal Code} [6 to 9 ]
else
{Customer.Postal Code}

I'd guess that the zip is NOT a numeric, rather a string, because as Ken pointed out, this can force assumptions to be made, and with Puerto Rico, which starts with 00...

If it is a numeric, you can make assumptions about the length and how to process using something akin to the above.

-k