+ Start a Discussion
Medhanie HabteMedhanie Habte 

Including commas and and's in formula for combining line items

Hi all,

I have a formula field that I built in a custom object that combines various fields into this one field for a mail merge. The custom object is called Product Donations, and there are about 12 fields referencing various line items that are referenced here. 
IF(ISBLANK(NEW_Letters__c),"", Letter_Count__c) 
&IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " handmade gators,") 
&IF(ISBLANK(Paracords__c),"", " "&TEXT(Paracords__c) & " paracord bracelets,") 
&IF(ISBLANK(Cool_Ties__c),"", " "&TEXT(Cool_Ties__c) & " handmade cool ties,") 
&IF(ISBLANK(Phone_Cards__c),"", " "&TEXT(Phone_Cards__c) & " phone cards,") 
&IF(ISBLANK(Battalion_Buddy__c),"", " "&TEXT(Battalion_Buddy__c) & " battalion buddies,") 
&IF(ISBLANK(Comic_Books__c),"", " comic books,") 
&IF(ISBLANK(Magazines__c),"", " magazines,") 
&IF(ISBLANK(Books__c),"", " books,") 
&IF(ISBLANK(Handmade_Cards__c),"", " handmade cards,") 
&IF(ISBLANK(Care_Kits__c),""," care kits,") 
&IF(ISBLANK(Gift_Cards__c),""," gift cards,") 
&IF(ISBLANK(Socks__c),"", " socks,") 
&IF(ISBLANK(Girl_Scout_Cookies__c),"", " "&TEXT(Girl_Scout_Cookies__c) & " boxes of Girl Scout cookies,") 
&IF(ISBLANK(Food_And_Snacks__c),"", " snack foods,") 
&IF(ISBLANK(Toys_Animals_Bears__c),""," assorted toys,") 
&IF(ISBLANK(Beanie_Babies__c),"", " "&TEXT(Beanie_Babies__c)& " Beanie Babies,") 
&IF(ISBLANK(Hats__c),"", " "&TEXT(Hats__c) & " handmade hats," ) 
&IF(ISBLANK(Scarves__c),"", " "&TEXT(Scarves__c)& " handmade scarves,") 
&IF(ISBLANK(Video_Games__c),"", " "&TEXT(Video_Games__c)& " video games,") 
&IF(ISBLANK(CDs_DVDs__c),"", " "&TEXT(CDs_DVDs__c)& " CD's,") 
&IF(ISBLANK(CDs_DVDs__c),"", " "&TEXT(DVDs__c)& " DVD's,") 
&IF(ISBLANK(Candy_lbs__c),"", " "&TEXT(Candy_lbs__c)& " Lbs of candy,") 
&IF(ISBLANK(Assorted_Items__c),""," assorted items,") 
&IF(ISBLANK(Recyclables__c),"", " recyclable items,")& " ", 



On the letter count, if there are additional itmes, I notice that a comma does not appear after it, it reads for example letters 1 scarves, 20 paracords, assorted items. Additionally, I am also looking for a way to include the word "and" after the comma if multiple line items exist. In this case, it should read as

letters 1 scarves, 20 paracords and assorted items.

Instead of 

letters 1 scarves, 20 paracords, assorted items

Also is there a formula to singularize items that have a 1 in it. For instance 1 scarves should just say scarf.

I can help with any further questions, even set up a trigger if needed. 

Hope this helps.
Medhanie HabteMedhanie Habte
For the letters section I found a possible workaround
IF(ISBLANK(NEW_Letters__c),"",NEW_Letters__c & " letters,") 
However, the comma still appears. It will read "20 letters,"