Reporting on Postcodes in Salesforce: how to create postal area, district and sector formula fields

Reporting on Postcodes in Salesforce: how to create postal area, district and sector formula fields

Postcodes can be tricky to report on in any CRM system, we hope this blog helps others analysing them in Salesforce.

Eircodes (Irish Postcodes)
The formulas below have been tested for UK postcodes, for Irish postcodes I think the formulas could be fairly easily amended for Eircodes which were introduced in 2014 – please see the Wikipedia page for more information on Irish postcodes.  If you create formula fields for Irish postcodes, perhaps you can add them onto a comment on this blog.

How is a UK Postcode formatted?
The UK postcode consists of five to seven alphanumeric characters and was created by Royal Mail.  On average, each full UK postcode represents about 15 properties.

This example below explains a UK postcode and it’s components:

• Full UK postcode: CV32 4RJ
• Postal Area: CV
• Postcode District: CV32
• Postcode Sector: CV32 4

Reporting on Postcodes in Salesforce
Using full postcodes in CRM reports can be too detailed to give any real insight – unless you are using a mapping or radius tool.

How to create Salesforce reports based on UK Postcodes
For Salesforce reports and dashboards, the creation of formula fields can be useful, especially when you have consumer or donor data.  Many of my clients use the Non Profit Success Pack so their contacts represent audiences and donors and using formula fields on the Contact object can be really useful for targeted communication and analysis.

These easy steps show you how to create summary fields for UK postal codes in Salesforce.  If you have international data, it may make sense to filter these formula fields first on country.

1. Create Formula Fields
To create formula fields in Salesforce, click the cog in the top right, select Setup, click Object Manager, click the desired object, e.g. Contact, then Fields & Relationships and click the New button.  If you haven’t created new fields before, you may choose not to include them on page layouts so watch out for this step – you can always add them to relevant page layouts later.

The formulas below are not reliant on the postcode space being in the correct place which is handy if you have any poorly formatted data.

2. Create a Formula Field called “Postcode Area”
When you click New field, first choose ‘Formula’ and then ‘Text’ as the type of formula field and create a field for Postal Area by copying and pasting the formula below:

IF( ISNUMBER( RIGHT( LEFT( MailingPostalCode , 2) , 1) ) , left(MailingPostalCode , 1), left(MailingPostalCode,2))

Because UK postcodes are either prefixed with one or two letters, the above formula solves this by saying ‘if the second character is a number, give me the first digit, otherwise give me the first two digits’.  For example, in the case of a Coventry postcode, it will give “CV” and in the case of a Birmingham postcode it will give “B”.

Here is a slightly amended version of the above formula which only runs the Postcode Area where the Country is United Kingdom or blank:

IF(
OR((MailingCountry = "United Kingdom"),(MailingCountry = "")) ,
IF( ISNUMBER( RIGHT( LEFT( MailingPostalCode , 2) , 1) ) , left(MailingPostalCode , 1), left(MailingPostalCode,2)),
"")

Or going one step further, the following formula removes postcode area for any postcodes that start with a number (and are therefore likely to be non-UK postcodes):

IF(
OR(BEGINS(MailingPostalCode, "1"),BEGINS(MailingPostalCode, "0"),BEGINS(MailingPostalCode, "2"),BEGINS(MailingPostalCode, "3"),BEGINS(MailingPostalCode, "4"),BEGINS(MailingPostalCode, "5"),BEGINS(MailingPostalCode, "6"),BEGINS(MailingPostalCode, "7"),BEGINS(MailingPostalCode, "8"),BEGINS(MailingPostalCode, "9"))
,
"",
IF(
OR((MailingCountry = "United Kingdom"),(MailingCountry = "")) ,
IF( ISNUMBER( RIGHT( LEFT( MailingPostalCode , 2) , 1) ) , left(MailingPostalCode , 1), left(MailingPostalCode,2)),
"")
)

3.  Create a Formula Field called “Postal District”
This formula simply removes the last 3 digits of the postcode and removes any spaces, e.g. CV32.  Follow the steps above and use the following formula:

trim(left( MailingPostalCode,(len(MailingPostalCode)-3)))

4.  Create a Formula Field called “Postcode Last 3”
This formula is known as the ‘Inward Code’ – i.e. the last three digits.  It is necessary to create a postal sector in the next step.  It’s a simple formula:

RIGHT( MailingPostalCode , 3)

5.  Create a Formula Field called “Postal Sector”
This provides everything excluding the last 2 digits of a postcode:

Postal_District__c &" "& left(Postcode_Last_3__c,1)

6.  Choose whether or not to add fields to Page Layouts
You can add your new fields to a page layout if you’d like to.  You may choose not to show ‘Postcode Last 3’ at all as it is not very useful – just a required step in creating Area, District and Sector.

7.  Create your Report or List View using your new Fields
Once your fields are created, it’s simple to bring them into Reports, Dashboards and List Views.

Splitting them by another criteria (such as a Checkbox) can reveal a particular Geography that needs attention on one of your KPIs:

Overview
This blog includes some simple, free formula fields that can be used to create geography analysis in Salesforce based on UK postcodes.  What techniques do you use to understand your UK data?