/
City State Country

City State Country

Owned by Jodie Miners
Last updated: Oct 16, 2023
2 min read

With State and Country Picklists

Accounts

IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+ IF(NOT(ISBLANK(TEXT(ShippingStateCode))),TEXT(ShippingStateCode)+" ","")+ IF(NOT(ISBLANK(TEXT(ShippingCountryCode))),TEXT(ShippingCountryCode)+" ", IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+ IF(NOT(ISBLANK(TEXT(BillingStateCode))),TEXT(BillingStateCode)+" ","")+ IF(NOT(ISBLANK(TEXT(BillingCountryCode))),TEXT(BillingCountryCode)+" ", ""))

Contacts

IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+ IF(NOT(ISBLANK(TEXT(OtherStateCode))),TEXT(OtherStateCode)+" ","")+ IF(NOT(ISBLANK(TEXT(OtherCountryCode))),TEXT(OtherCountryCode)+" ", IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+ IF(NOT(ISBLANK(TEXT(MailingStateCode))),TEXT(MailingStateCode)+" ","")+ IF(NOT(ISBLANK(TEXT(MailingCountryCode))),TEXT(MailingCountryCode)+" ", ""))

Without State and Country Picklists

Accounts

IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+  IF(NOT(ISBLANK(ShippingState)),ShippingState+" ","")+  IF(NOT(ISBLANK(ShippingCountry)),ShippingCountry+" ",  IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+  IF(NOT(ISBLANK(BillingState)),BillingState+" ","")+  IF(NOT(ISBLANK(BillingCountry)),BillingCountry+" ",  ""))


Or depending on how much garbage you have in your Addresses, then you may need to reduce the formula to under 76 Characters to show it on a list view - yep, that's a thing apparently. So I also removed the Country if it's Aus

LEFT(  IF(NOT(ISBLANK(ShippingCity)),ShippingCity+" ","")+  IF(NOT(ISBLANK(ShippingState)),ShippingState+" ","")+  IF(ShippingCountry = "Australia","",IF(NOT(ISBLANK(ShippingCountry)),ShippingCountry+" ",  IF(NOT(ISBLANK(BillingCity)),BillingCity+" ","")+  IF(NOT(ISBLANK(BillingState)),BillingState+" ","")+  IF(BillingCountry = "Australia","",IF(NOT(ISBLANK(BillingCountry)),BillingCountry+" ",  ""))))  ,75)

Contacts

IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+  IF(NOT(ISBLANK(OtherState)),OtherState+" ","")+  IF(NOT(ISBLANK(OtherCountry)),OtherCountry+" ",  IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+  IF(NOT(ISBLANK(MailingState)),MailingState+" ","")+  IF(NOT(ISBLANK(MailingCountry)),MailingCountry+" ",  "")) LEFT( IF(NOT(ISBLANK(OtherCity)),OtherCity+" ","")+ IF(NOT(ISBLANK(OtherState)),OtherState+" ","")+ IF(LOWER(OtherCountry) = "australia","",IF(NOT(ISBLANK(OtherCountry)),OtherCountry+" ", IF(NOT(ISBLANK(MailingCity)),MailingCity+" ","")+ IF(NOT(ISBLANK(MailingState)),MailingState+" ","")+ IF(LOWER(MailingCountry) = "australia","",IF(NOT(ISBLANK(MailingCountry)),MailingCountry+" ", "")))) ,75)

State Only

For Australia and New Zealand, and only showing State Codes - ideal for integration with other apps.

IF(ISBLANK(MailingState), IF( OR(Lower(Account.BillingCountry) = "australia",Account.BillingCountry = "AUS"), CASE(Account.BillingState, "New South Wales","NSW", "Victoria","VIC", "Queensland","QLD", "Tasmania","TAS", "Western Australia","WA", "South Australia","SA", "Northern Territory","NT", "Australian Capital Territory","ACT",Account.BillingState), IF(OR(Lower(Account.BillingCountry) = "new zealand",Account.BillingCountry = "NZ"),"NZ", IF(NOT(ISBLANK(Account.BillingCountry)),"International",""))), IF( OR(lower(MailingCountry) = "australia",MailingCountry = "AUS"), CASE(MailingState, "New South Wales","NSW", "Victoria","VIC", "Queensland","QLD", "Tasmania","TAS", "Western Australia","WA", "South Australia","SA", "Northern Territory","NT", "Australian Capital Territory","ACT",MailingState), IF(OR(Lower(MailingCountry) = "new zealand",MailingCountry = "NZ"),"NZ", IF(NOT(ISBLANK(MailingCountry)),"International",""))))

NOTE

  • Sometimes you might want to include the Account City State and Country in the Contact formula also - so if Contact is blank, it gets the details from the Account. 

  • Use this field in all cases in list views rather than Mailing State

  • I often remove the second address on Contacts, so modify this formula to suit. 

  • If you are using NPSP address management, then this might not be correct. 

  • You should always enable State and Country Picklists unless you have a recalcitrant app that has not enabled it yet - get onto them, there is no excuse! 

  • Rename your State and Country fields away from Shipping and Billing - see Organisations

Related content