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.