You need to sign in to do that

Don't have an account?

Chad Ritchie

# Choosing first instance of substitution formula

Hey guys,

I'm trying to figure out how to get this formula to work on just the first ",and" and not the second. Here's what I have: Chad, and Chris, and Sean.

I would like it to return Chad, Chris, and Sean by simply replacing ", and" with ",". And keep in mind that sometimes it brings back 1 name, sometimes 2, and sometimes 3, so I'd like to apply for each of those. Here's what I have now:

I'm trying to figure out how to get this formula to work on just the first ",and" and not the second. Here's what I have: Chad, and Chris, and Sean.

I would like it to return Chad, Chris, and Sean by simply replacing ", and" with ",". And keep in mind that sometimes it brings back 1 name, sometimes 2, and sometimes 3, so I'd like to apply for each of those. Here's what I have now:

**SUBSTITUTE(LEFT(test__c, 40),", and",",") but unfortuntely this always does it to both instances so it will return Chad, Chris, Sean, instead of adding an and after Chris. Any help is appreciated!!! I know this is really easy in excel because you can indicate the instance that you want it to apply to, but not sure about doing this on Salesforce. Thanks guys!!**
SathishPanjalaTry this ...

LEFT(test__c, Find(",", test__c)-1)

Please mark the answer as solved if it works.

Sathish P

Chad RitchieThanks for the response, unfortunately I'm getting the compiled formula too big error, so I'm working on getting around that so that I can test your formula. Chad RitchieYeah, my formula is LEFT(Strategy_just_used_for_update_email__c,Find(",",Strategy_just_used_for_update_email__c)-1) This formula pulls from another field which has the formula : IF(INCLUDES(Strategy__c, "A"), "A, and", NULL)+ IF(INCLUDES(Strategy__c, "B"), "B, and ", NULL)+ IF(INCLUDES(Strategy__c, "C"), "C, and", NULL) + IF(INCLUDES(Strategy__c, "D"), "D, and ", NULL) + IF(INCLUDES(Strategy__c, "E"), "E, and ", NULL) + IF(INCLUDES(Strategy__c, "F"), "F, and ", NULL) + IF(INCLUDES(Strategy__c, "G"), "G, and", NULL) + IF(INCLUDES(Strategy__c, "H"), "H, and" NULL) + IF(INCLUDES(Strategy__c, "I"), "I, and ", NULL) + IF(INCLUDES(Strategy__c, "J"), "J, and ", NULL) + IF(INCLUDES(Strategy__c, "K"), "K, and ", NULL) + IF(INCLUDES(Strategy__c, "L"), "L, and ", NULL) + IF(INCLUDES(Strategy__c, "M"), "M, and ", NULL) + IF(INCLUDES(Strategy__c, "N"), "N, and ", NULL) + IF(INCLUDES(Strategy__c, "O"), "O, and ", NULL) + IF(INCLUDES(Strategy__c, "P"), "P, and ", NULL)

The reason it's so long, and likely causing the overflow error is that it is processing a multipicklist. So basically I need to figure out a way to get it to return (based on the number of values selected): 1) A 2) A and B 3) A, B, and C 4) A,B,C, and D

It's a tough puzzle to solve, all help is appreciated though. Thanks guys!!!

Chad RitchieCould you explain that concept to me? I know what a trigger is, but you're saying storing the value in there will save me some space on the formula?