Formula to format zip codes 1

[dpm1234]

Hi,

I'm sure this has been done before so...

I have 5 and 9 digit zip codes stored in a database without any hyphens. I would like to insert one when necessary. Some of the zip codes are not well formed, in that case I would like to do nothing.

i.e.
12345 => 12345
1234567 => 1234567
123456789 => 12345-6789

Anyone have a formula that would do this for me?

 

[synapsevampire]

I'll assume that it's stored as a string:

if len(trim({table.field}) = 9 then
picture({table.field},"99999-9999&quot
else
{table.field}

-k

 

[vidru]

And if that doesn't work, change the 9's to x's:

if len(trim({table.field}) = 9 then
picture({table.field},"xxxxx-xxxx&quot
else
{table.field}

-dave

 

[dpm1234]

Thanks! That worked great!!