0

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

enter image description here

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.

0

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',
                ''
            )
        )
    )
)
2
  • Hi there, so this is my whole formula (i.imgur.com/pnNIKwS.png). It's 3794 characters long, and when I click "Check Syntax", it says it's all good. However once I try to save, I get this: "Error: Compiled formula is too big to execute (9,146 characters). Maximum size is 5,000 characters" Any idea what's going on and how I can fix this? – user256515 Nov 16 '20 at 16:21
  • @user256515, I guess there are too many IF+BEGINS functions used in your formula. I am not sure how it calculates the compiled size though. Anyway, have you tried the example that I provided? – Hengky Ilawan Nov 17 '20 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.