Ability to create a formula field that concatenates when data in input to a field

Question

I found a workaround to do this, however, there are a few minor tweaks I need to determine in my formula structure.

I created this formula, which concatenates and populates data based on if this field has valid

IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " Gators ")& IF(ISBLANK(Paracords__c),"", ", "&TEXT(Paracords__c) & " Paracords ")& IF(ISBLANK(Cool_Ties__c),"", ", "&TEXT(Cool_Ties__c) & " Cool Ties ")& IF(ISBLANK(Phone_Cards__c),"", ", "&TEXT(Phone_Cards__c) & " Phone Cards ")& IF(ISBLANK(Gift_Cards__c),"", ", "&TEXT(Gift_Cards__c) & " Gift Cards worth &TEXT(Gift_Cards_Value__c) ")& IF(ISBLANK(Total_Letters__c),"", ", "&TEXT(Total_Letters__c)& " Letters ")& IF(ISBLANK(Girl_Scout_Cookies__c),"", ", "&TEXT(Girl_Scout_Cookies__c) & " Girl Scout Cookies ")& IF(ISBLANK(Food_And_Snacks__c),"", ", "&TEXT(Food_And_Snacks__c) & " Food And Snack Items ")& IF(ISBLANK(Toys_Animals_Bears__c),"", ", "&TEXT(Toys_Animals_Bears__c)& " Toys, Animals And Bears ")& IF(ISBLANK(Beanie_Babies__c),"", ", "&TEXT(Beanie_Babies__c)& " Beanie Babies ")& IF(ISBLANK(Hats__c),"", ", "&TEXT(Hats__c) & " Hats " )& IF(ISBLANK(Scarves__c),"", ", "&TEXT(Scarves__c)& " Scarves ")& IF(ISBLANK(Video_Games__c),"", ", "&TEXT(Video_Games__c)& " Video Games ")& IF(ISBLANK(CDs_DVDs__c),"", ", "&TEXT(CDs_DVDs__c)& " CD's & DVD's ")& IF(ISBLANK(Candy_lbs__c),"", ", "&TEXT(Candy_lbs__c)& " Lbs of Candy ")& IF(ISBLANK(Recyclables__c),"", ", "&TEXT(Recyclables__c)& " Recyclable Items ")& IF(ISBLANK(Other_Special_Items__c),"", ", "&TEXT(Other_Special_Items__c)& " Other Items ")

Ultimately, here's the result I get

13 Gators , 16 Paracords , 14 Cool Ties , 1 Phone Cards , 3 Gift Cards worth &TEXT(Gift_Cards_Value__c) , 42 Letters , 5 Girl Scout Cookies , 7 Food And Snack Items , 8 Toys, Animals And Bears , 2 Beanie Babies , 15 Hats , 17 Scarves , 19 Video Games , 20 CD's & DVD's , 21 Lbs of Candy , 23 Recyclable Items , 22 Other Items

So basically, while the formula is near perfect, I am looking to determine a way to omit the first comma in the formula (for instance If I provide no value in Gators, it reads , 16 Paracords...), and if possible include an and preceding the last value (i.e. and 22 Other Items) Ideally, I vision something that looks like this....

"16 Paracords and 22 Other Items"

This as a result concatenates the data into a digestible, readable result, which can be included in printed documentation, mail-merge letters and the like. If it isn't possible, no big deal.

Additionally, I am also determining a way to populate the value "Gift Cards Value" within the text so it reads i.e. "1 Gift Card worth $1.00).

Thanks very much.

Answer 1

Yes it is possible, you would need to combine the following types of functions:

1. IF(logical_test, value_if_true, value_if_false) and replace logical_test with the expression you want evaluated; replace value_if_true with the value you want returned if the expression is true; replace value_if_false with the value you want returned if the expression is false.

2. The ISBLANK
   ISBLANK(expression) and replace expression with the expression you want evaluated.

3. & (Concatenate)

   Description: Connects two or more strings.

   Use: string1&string2 and replace each string with merge fields, expressions, or other values.

   Example: "Expense-" & Trip_Name__c & "-" & ExpenseNum__c

   This formula displays the text “Expense-” followed by trip name and the expense number. This is a text formula field that uses an expense number custom field.

Answer 2

I found a workaround to do this, however, there are a few minor tweaks I need to determine in my formula structure.

I created this formula, which concatenates and populates data based on if this field has valid

IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " Gators ")& IF(ISBLANK(Paracords__c),"", ", "&TEXT(Paracords__c) & " Paracords ")& IF(ISBLANK(Cool_Ties__c),"", ", "&TEXT(Cool_Ties__c) & " Cool Ties ")& IF(ISBLANK(Phone_Cards__c),"", ", "&TEXT(Phone_Cards__c) & " Phone Cards ")& IF(ISBLANK(Gift_Cards__c),"", ", "&TEXT(Gift_Cards__c) & " Gift Cards worth &TEXT(Gift_Cards_Value__c) ")& IF(ISBLANK(Total_Letters__c),"", ", "&TEXT(Total_Letters__c)& " Letters ")& IF(ISBLANK(Girl_Scout_Cookies__c),"", ", "&TEXT(Girl_Scout_Cookies__c) & " Girl Scout Cookies ")& IF(ISBLANK(Food_And_Snacks__c),"", ", "&TEXT(Food_And_Snacks__c) & " Food And Snack Items ")& IF(ISBLANK(Toys_Animals_Bears__c),"", ", "&TEXT(Toys_Animals_Bears__c)& " Toys, Animals And Bears ")& IF(ISBLANK(Beanie_Babies__c),"", ", "&TEXT(Beanie_Babies__c)& " Beanie Babies ")& IF(ISBLANK(Hats__c),"", ", "&TEXT(Hats__c) & " Hats " )& IF(ISBLANK(Scarves__c),"", ", "&TEXT(Scarves__c)& " Scarves ")& IF(ISBLANK(Video_Games__c),"", ", "&TEXT(Video_Games__c)& " Video Games ")& IF(ISBLANK(CDs_DVDs__c),"", ", "&TEXT(CDs_DVDs__c)& " CD's & DVD's ")& IF(ISBLANK(Candy_lbs__c),"", ", "&TEXT(Candy_lbs__c)& " Lbs of Candy ")& IF(ISBLANK(Recyclables__c),"", ", "&TEXT(Recyclables__c)& " Recyclable Items ")& IF(ISBLANK(Other_Special_Items__c),"", ", "&TEXT(Other_Special_Items__c)& " Other Items ")

Ultimately, here's the result I get

13 Gators , 16 Paracords , 14 Cool Ties , 1 Phone Cards , 3 Gift Cards worth &TEXT(Gift_Cards_Value__c) , 42 Letters , 5 Girl Scout Cookies , 7 Food And Snack Items , 8 Toys, Animals And Bears , 2 Beanie Babies , 15 Hats , 17 Scarves , 19 Video Games , 20 CD's & DVD's , 21 Lbs of Candy , 23 Recyclable Items , 22 Other Items

So basically, while the formula is near perfect, I am looking to determine a way to omit the first comma in the formula (for instance If I provide no value in Gators, it reads , 16 Paracords...), and if possible include an and preceding the last value (i.e. and 22 Other Items) Ideally, I vision something that looks like this....

"16 Paracords and 22 Other Items"

This as a result concatenates the data into a digestible, readable result, which can be included in printed documentation, mail-merge letters and the like. If it isn't possible, no big deal.

Additionally, I am also determining a way to populate the value "Gift Cards Value" within the text so it reads i.e. "1 Gift Card worth $1.00).

Thanks very much.