Skip to main content

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

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

That is perfect. Thanks so much! Scott

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

 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,'-',''))

 

 

Reply


Terms & Conditions