Help

Format Zip Codes

Topic Labels: Formulas
Solved
Jump to Solution
1089 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,'-',''))