Formula Field: how to associate UK Postcode to UK Region?

Question

I have a Postcode__c text field on the Account object.

I need to create a Region__c field that contains name of the region corresponding to the Postcode__c. This is determined via the first few characters of the postcode, meaning if the postcode is SE2 2UN, then the Region__c should be "Greater London" (because SE = Greater London).

This is what the mapping looks like in Google Sheets (left column is the postcode prefix, right column is the corresponding region).

I'm thinking of making Region__c a formula field be, but what should the formula be? I know I might need this for example: LEFT(Postcode__c, LEN(Postcode__c) -4 ) because the last 4 characters are always white space, 1 digit, 2 letters, and we can dismiss that (this is the suffix of the postcode), we only need the prefix which is the bit before these 4 characters.

And then imbricated IF conditions, probably.

But I can't really use "BEGINS" because then what happens when you have prefixes that share a similar beginning, for example "S" is East Midlands but "SE" is Greater London. See full list here: https://www.robertsharp.co.uk/2017/08/09/a-table-that-shows-the-uk-region-for-all-postcode-districts/

Another problem is the character limit in formulas, I would imagine.

Answer 1

You can combine IF and CONTAINS function to achieve this.

You need to put 2-char prefix in higher priority. So, formula for 2-char prefix should be evaluated first.

The formula below is just an example, it's not complete.

IF (
    CONTAINS('|BF|BX|QC|XX|', LEFT(Postcode__c,2)), 'Non-geographic',
    IF (
        CONTAINS('|DF|DL|', LEFT(Postcode__c,2)), 'North East',
        IF (
            CONTAINS('|S|', LEFT(Postcode__c,1)), 'East Midlands',
            IF (
                CONTAINS('|E|N|W|', LEFT(Postcode__c,1)), 'Greater London',
                ''
            )
        )
    )
)