Ask Search:
salesforce instancesalesforce instance 

Formula Based on Begins

I am trying to create a formula field on Lead object that is mapped to contact object.
Acquisition Program-Mkt71 is a text field. Based on the contents of the field I'd like to populate "Acquisition Region" with the criteria below:
If Acquisition Program-Mkt71 starts with "AP " then "Acquisition Region"  should show APAC.

like wise i need to create the following

 *  Starts with "AP" = APAC

*         Starts with "BE" or "NL" = Benelux

*         Starts with "U4D" or "U4" = Global

*         Starts with "FR" = France

*         Starts with "DE" = Germany

*         Starts with "HU" = Hungary

*         Starts with "SP" or "PT" = Iberia

*         Starts with "SE" or "DK" or "NO" or "FI" = Nordics

*         Starts with "NA" = North America

*         Starts with "PL" = Poland

*         Starts with "UKI" = UKI

I have created a formula field as below


IF(LEFT(mkto71_Acquisition_Program__c, 1) ="AP", "APAC", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="BE", "Benelux", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="U4D", "Global", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="FR", "France", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="DE", "Germany", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="HU", "Hungary", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="SE", "Nordics", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="NA", "North America", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="PL", "Poland", 
IF(LEFT(mkto71_Acquisition_Program__c, 1) ="UKI", "UKI","Error"))))))))))

but it is returning errors for all the records

Please suggest
 
Best Answer chosen by salesforce instance
Steve MolisSteve Molis
PS.  For your second question you would just amend your Formula like this
IF(BEGINS(mkto71_Acquisition_Program__c,"AP"), "APAC", 
IF(BEGINS(mkto71_Acquisition_Program__c,"BE"), "Benelux", 
IF(BEGINS(mkto71_Acquisition_Program__c,"U4"), "Global", 
IF(BEGINS(mkto71_Acquisition_Program__c,"FR"), "France", 
IF(BEGINS(mkto71_Acquisition_Program__c,"DE"), "Germany", 
IF(BEGINS(mkto71_Acquisition_Program__c,"HU"), "Hungary", 
IF(BEGINS(mkto71_Acquisition_Program__c,"SE"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"DK"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"NO"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"FI"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"NA"), "North America", 
IF(BEGINS(mkto71_Acquisition_Program__c,"PL"), "Poland", 
IF(BEGINS(mkto71_Acquisition_Program__c,"UKI"), "UKI",
"Error")))))))))))))

 

All Answers

Steve MolisSteve Molis
Try this 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "AP", "APAC", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "BE", "Benelux", 
IF(LEFT(mkto71_Acquisition_Program__c, 3) = "U4D", "Global", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "FR", "France", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "DE", "Germany", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "HU", "Hungary", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "SE", "Nordics", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "NA", "North America", 
IF(LEFT(mkto71_Acquisition_Program__c, 2) = "PL", "Poland", 
IF(LEFT(mkto71_Acquisition_Program__c, 3) = "UKI", "UKI",
"Error"))))))))))

 
salesforce instancesalesforce instance
Thanks Steve..it works but in the condition i have to include Starts with "U4D" or "U4" = Global,Starts with "BE" or "NL" = Benelux,Starts with "SE" or "DK" or "NO" or "FI" = Nordics..but the formula shows only one value but not OR 

Please letme know.
Sunil SarillaSunil Sarilla
+ Steve
or you could case function as below
CASE(LEFT(mkto71_Acquisition_Program__c,2),
"AP", "APAC",
"BE", "Benelux",
"U4", "Global",
"FR", "France",
"DE", "Germany",
"HU", "Hungary",
"SE", "Nordics",
"NA", "North America",
"PL", "Poland",
"UK", "UKI",
"Error")
Steve MolisSteve Molis
On second thought try this one instead
IF(BEGINS(mkto71_Acquisition_Program__c,"AP"), "APAC", 
IF(BEGINS(mkto71_Acquisition_Program__c,"BE"), "Benelux", 
IF(BEGINS(mkto71_Acquisition_Program__c,"U4D"), "Global", 
IF(BEGINS(mkto71_Acquisition_Program__c,"FR"), "France", 
IF(BEGINS(mkto71_Acquisition_Program__c,"DE"), "Germany", 
IF(BEGINS(mkto71_Acquisition_Program__c,"HU"), "Hungary", 
IF(BEGINS(mkto71_Acquisition_Program__c,"SE"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"NA"), "North America", 
IF(BEGINS(mkto71_Acquisition_Program__c,"PL"), "Poland", 
IF(BEGINS(mkto71_Acquisition_Program__c,"UKI"), "UKI",
"Error"))))))))))

 
Steve MolisSteve Molis
PS.  For your second question you would just amend your Formula like this
IF(BEGINS(mkto71_Acquisition_Program__c,"AP"), "APAC", 
IF(BEGINS(mkto71_Acquisition_Program__c,"BE"), "Benelux", 
IF(BEGINS(mkto71_Acquisition_Program__c,"U4"), "Global", 
IF(BEGINS(mkto71_Acquisition_Program__c,"FR"), "France", 
IF(BEGINS(mkto71_Acquisition_Program__c,"DE"), "Germany", 
IF(BEGINS(mkto71_Acquisition_Program__c,"HU"), "Hungary", 
IF(BEGINS(mkto71_Acquisition_Program__c,"SE"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"DK"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"NO"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"FI"), "Nordics", 
IF(BEGINS(mkto71_Acquisition_Program__c,"NA"), "North America", 
IF(BEGINS(mkto71_Acquisition_Program__c,"PL"), "Poland", 
IF(BEGINS(mkto71_Acquisition_Program__c,"UKI"), "UKI",
"Error")))))))))))))

 
This was selected as the best answer
salesforce instancesalesforce instance
Thanks Steve