Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Format Zip Codes

Topic Labels: Formulas
Solved
Jump to Solution
2091 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Brasted
7 - App Architect
7 - App Architect

Greetings, I have a zip code field that's either 5 digits or 9 digits. I need a formula to display the zip code as either 12345 or 12345-6789 depending on the content of the field. I know How to write the beginning of the formula, I do not know how to write the if 9 part. 

 I have IF({Zip Code} <6,{Zip Code}, Need to describe the 9 digit code with a dash.)

Anyone have a thought on this? I am using a number field for the zip code. Is this the correct field to use?

Thanks, Scott

2 Solutions

Accepted Solutions
Databaser
12 - Earth
12 - Earth

It could probably made shorter, but this should work. I imagine the Zip code field is a number field. 

IF({Zip Code}, IF(LEN({Zip Code}&"")=5, {Zip Code}, CONCATENATE(LEFT({Zip Code}&"", 5), "-", RIGHT({Zip Code}&"", 4))))

See Solution in Thread

add to empty string is a way to convert value to a text. usually used for lookups and similar stuff, but here it converts to a text from a number.
If your field were text, formula might be shorter

 

LEFT({Zip Code},5) & IF(LEN({Zip Code})>5, '-' & RIGHT({Zip Code},4))

 

 or this (funny that we often use SUBSTITUTE() to remove word part, and here REPLACE() used for insert)

 

REPLACE({Zip Code},6,0,IF(LEN({Zip Code})>5,'-',''))

 

 

See Solution in Thread

4 Replies 4
Databaser
12 - Earth
12 - Earth

It could probably made shorter, but this should work. I imagine the Zip code field is a number field. 

IF({Zip Code}, IF(LEN({Zip Code}&"")=5, {Zip Code}, CONCATENATE(LEFT({Zip Code}&"", 5), "-", RIGHT({Zip Code}&"", 4))))
Scott_Brasted
7 - App Architect
7 - App Architect

That is perfect. Thanks so much! Scott

 CanI ask you to clarify something for me.  I understand what you did here. But can you tell me the purpose of the   & " "   after {Zip Code} does? Thank, Scott

add to empty string is a way to convert value to a text. usually used for lookups and similar stuff, but here it converts to a text from a number.
If your field were text, formula might be shorter

 

LEFT({Zip Code},5) & IF(LEN({Zip Code})>5, '-' & RIGHT({Zip Code},4))

 

 or this (funny that we often use SUBSTITUTE() to remove word part, and here REPLACE() used for insert)

 

REPLACE({Zip Code},6,0,IF(LEN({Zip Code})>5,'-',''))