Hi All,

Just wondering if there's a way to simplify the formula I am working on.

To give you a background, I am trying to create a formula that will execute the standard address formatting that will be pulled to a Conga template.
There are 2 types of address consists of fields namely:
1. Postal Address
              PO Box<br>
              City<double space>State<double space>Postcode<br>
              Country
2. Residential Address
              Building/property name<double space>Flat Unit Details<double space>Street No.<double space>Street Name<br>
              City<double space>State<double space>Postcode<br>
              Country

The formula checks if any of postal address fields have values, so
              if YES, it will return the concatenated postal address
              if NO, it will check if Building/property name or Flat Unit details have values
                         so if YES, then it will display all the address fields for the residential address
                               if NO, then it will only display Street No. and Street name on the first line of the residential address and the rest of the designated fields


Here's the actual formula:
IF(

OR(
Other_City__c <> "", Other_Country__c <> "", Other_Street__c <> "", Other_Zip_Postal_Code__c <> "", Other_State_Provience__c <> "" ),
UPPER(Other_Street__c) & "  " & UPPER(Postal_street_number__c) & "  " & UPPER(Postal_street_name__c) & "<br>" & UPPER(Other_City__c ) & "  " & UPPER(CASE( Other_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & "  " & Other_Zip_Postal_Code__c & "<br>" & UPPER(Other_Country__c ),

IF(OR(Address_building_property_name__c <> "", Address_flat_unit_details__c <> ""),

UPPER(Address_building_property_name__c) & "  " & UPPER(Address_flat_unit_details__c) & "  " & UPPER(Address_street_number__c) & "  " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & "  " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & "  " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c ),

UPPER(Address_street_number__c) & "  " & UPPER(Address_street_name__c) & "<br>" & UPPER(Mailing_City__c) & "  " & UPPER(CASE( Mailing_State_Provience__c , "Queensland", "QLD", "QLD", "QLD", "Western Australia", "WA", "WA", "WA", "Australian Capital Territory", "ACT", "ACT", "ACT", "New South Wales", "NSW", "NSW", "NSW", "Northern Territory", "NT", "NT", "NT", "South Australia","SA", "SA", "SA", "Tasmania", "TAS", "TAS", "TAS", "Victoria", "VIC", "VIC", "VIC", "Other (Overseas but not an Australian territory or dependency)", "OVERSEAS", "OVERSEAS", "OVERSEAS", "OTHER" )) & "  " & Mailing_Zip_Postal_Code__c & "<br>" & UPPER(Mailing_Country__c )
)
)

You may notice that there are case methods on the formula which eat up a lot on the editor so to explain it, the 'State' data on the app was not uniformed so I had to check both formats (Complete State name and Short name) and return only one (Short name will be returned).

Problem: I cannot save the formula I created because it exceeds 5000 characters on the editor.
To-do: Simplify the formula

Any idea or suggestion to solve this? Thank you very much.