ShowAll Questionssorted byDate Posted

# 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!!
SathishPanjala
Try this ...

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

Sathish P
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.
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!!!