Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Chad RitchieChad 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: 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!!
SathishPanjalaSathishPanjala
Try this ...

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

Please mark the answer as solved if it works.

Sathish P
Chad RitchieChad Ritchie
Thanks 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 RitchieChad Ritchie
Yeah, 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 RitchieChad Ritchie
Could 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?