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

Question

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.

SUBSTITUTE( 
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) & " 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,")& " ", 

",.", 

"" 
)

I am also looking for a way to include the word "and" after the comma if multiple line items exist and leave out the numerical value if the value equals 1. The result should read like this.

letters, 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.

Answer 1

There are many ways to do what you want, it entirely depends on how you want to approach obtaining the solution. RegEx would be the way to go if you want to try to do it within your formula.

If you use RegEx the following would create a capture group that captures only the first string until a comma occurs:

^((([\w+?\s+\w+$])+)(,){1}) 

You could then add the and using either RegEx Substitution, String Methods or Pattern & Matcher class methods; the latter two being non-formula solutions.

The following regular expression will find a '1' provided it has at least one single space both before and after it:

((\s)(1){1}(\s){1}) 

You could utilize this to find your 1's and remove them using either RegEx Substitution, String methods or Pattern & Matcher class methods.

As examples of string methods, you could use string.split(',') to split your string at every comma without the need to use RegEx. You could also use split(regExp) to split by capture group.

substringBefore(separator) would return the substring that occurs before the first occurrence of the specified separator. You could utilize this to find the location to place your and or remove your 1's if combined the command with substringAfter(separator). Again, string methods would not apply to a formula solution.