0

I want to create a formula (text) that combines (parts) of two fields in some scenarios.

Example: If the 'BillingPostalCode__c' starts with 90 and the first 9 characters of the 'NormalisedCompanyName__c' equals SIEMENSAG then I would like to combine the first 3 characters of the 'BillingStreetCorrected__c' with the 'NormalisedCompanyName__c'.

NormalisedCompanyName__c (text) = SIEMENSAG_Some_Text

BillingPostalCode__c (formnula, 154 chars) = 90123

BillingStreetCorrected__c (text) = MyStreet

Formula Result: MySSIEMENSAG_Some_Text

Unfortunately, right now I do not see any other solution than repeating 'BillingStreetCorrected__c' million times. I'm close to Compiled formula is too big to execute Limit but I need to add additional case results.

I hope that anyone has a suggestion to reduce characters and/or to build a more beautiful formula!?

Best, Stefan

LEFT(
CASE(
    LEFT(
        LEFT(BillingPostalCode__c, 2) + 
        NormalisedCompanyName__c,
        11), 
    '38VOLKSWAGE', SUBSTITUTE(BillingStreetCorrected__c, 'BRIEFFACH', ''),
    '91SIEMENSAG', BillingStreetCorrected__c,
    '90SIEMENSAG', BillingStreetCorrected__c,
    '70DAIMLERAG', BillingStreetCorrected__c,
    '81SIEMENSAG', BillingStreetCorrected__c,
    '45SIEMENSAG', BillingStreetCorrected__c,
    '13SIEMENSAG', BillingStreetCorrected__c,
    '71DAIMLERAG', BillingStreetCorrected__c,
    '10DAIMLERAG', BillingStreetCorrected__c,
    '10DEUTSCHEB', BillingStreetCorrected__c,
    '53DEUTSCHET', BillingStreetCorrected__c,
    '76SIEMENSAG', BillingStreetCorrected__c,
    '80BMWAG', BillingStreetCorrected__c,
    '85AUDIAG', BillingStreetCorrected__c,
    '67BASFSE', BillingStreetCorrected__c,
    '01TECHNISCH', BillingStreetCorrected__c,
    ''
    ),3)
+ NormalisedCompanyName__c
  • Hi Stefan - for clarity: are you running into the "maximum number of characters" limit, or the "compiled formula is too big to execute" limit? If it's the compile size limit, are BillingStreetCorrected__c or NormalisedCompanyName__c also formula fields? Also, you may want to check out the Salesforce doc on reducing formula sizes: resources.docs.salesforce.com/218/latest/en-us/sfdc/pdf/… – shannonsans Feb 26 at 23:35
  • Hi @shannonsans, thanks for your question. I edited the post. 1. BillingStreetCorrected__c, NormalisedCompanyName__c are text fields. BillingPostalCode__c is a more or less tiny formula (154 chars). 2. Error Message is "Compiled formula is too big to execute". Thanks for the link. :-) Best, Stefan – Stefan Feb 27 at 7:51

Your Answer

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

Browse other questions tagged or ask your own question.