Text Formula String Concatenation - Omit Trailing Comma

Question

Scenario : I have 7 checkbox fields. For example say it is Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. In Formula field I want to display all that are true as comma separated text in order from Sunday thru Saturday.

For example:

Sunday - True
Friday - True

Formula Field should be - Sunday, Friday.

I tried below formula for concatenating but comma's are coming extra if any one of the field is false.

IF( Sunday__c = true, 'Sunday', '') + ','+
IF( Monday__c= true , 'Monday', '')+ ','+ 
IF( Tuesday__c= true , 'Tuesday', '')+ ','+ 
IF( Wednesday__c= true , 'Wednesday', '')+ ','+ 
IF( Thursday__c= true , 'Thursday', '')+ ','+
IF( Friday__c= true , 'Friday', '')+ ','+ 
IF( Saturday__c= true , 'Saturday', '')

Note : I don't want to go for trigger

Answer 1

I think this is how far you have to go to implement formula here. I'm doubtful its compile size will be under the limit, but I haven't checked.

IF(Sunday__c, 'Sunday' & IF(
    OR(Monday__c, Tuesday__c, Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Monday__c, 'Monday' & IF(
    OR(Tuesday__c, Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Tuesday__c, 'Tuesday' & IF(
    OR(Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Wednesday__c, 'Wednesday' & IF(
    OR(Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Thursday__c, 'Thursday' & IF(
    OR(Friday__c, Saturday__c), ',', ''
), '') &
IF(Friday__c, 'Friday' & IF(Saturday__c, ',', ''), '') &
IF(Saturday__c, 'Saturday', '')

---

A couple notes:

• You don't need to check Checkbox__c = true for a checkbox in a formula. Just use Checkbox__c.
• The proper string concatenation operator is a single ampersand (&).

Answer 2

@AdrianLarson I used your suggestion for a similar solution and it worked for a year or so, until I was asked to include more Checkboxes (12 in all) in the formula, then I got:

Error: Compiled formula is too big to execute (5,713 characters). Maximum size is 5,000 characters.

I found a simpler solution. Include the ", " in each result, then add a period to the end of your IF statements. Wrap the code with SUBSTITUTE to replace ", ." with null in order to remove the trailing comma at the end of your text

SUBSTITUTE(
IF( Sunday__c, 'Sunday, ','') &
IF( Monday__c, 'Monday, ','') &
IF( Tuesday__c, 'Tuesday, ','') &
IF( Wednesday__c, 'Wednesday, ','') &
IF( Thursday__c, 'Thursday, ','') &
IF( Friday__c, 'Friday, ','') &
IF( Saturday__c, 'Saturday, ','') &
'.'),
', .', NULL)

Answer 3

You can try the following logic -

IF( Sunday__c , 'Sunday ', '') + 
IF( Sunday__c & Monday__c , ', Monday', IF( Monday__c , 'Monday', '') ) +
IF (Tuesday__c & (Sunday__c OR Monday__c ), ', Tuesday' , IF(Tuesday__c , 'Tuesday', '') )+
So on...

This will add the commas only when it has any former value.

Hope this helps.

Answer 4

Another solution would be to use the SUBSTITUTE function to break repeated commas down to single commas, and then use the MID function to get rid of the leading and trailing commas:

MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(','&IF(Sunday__c,'Sunday','')&','&IF(Monday__c,'Monday','')&','&IF(Tuesday__c,'Tuesday','')&','&IF(Wednesday__c,'Wednesday','')&','&IF(Thursday__c,'Thursday','')&','&IF(Friday__c,'Friday','')&','&IF(Saturday__c,'Saturday','')&',',",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(','&IF(Sunday__c,'Sunday','')&','&IF(Monday__c,'Monday','')&','&IF(Tuesday__c,'Tuesday','')&','&IF(Wednesday__c,'Wednesday','')&','&IF(Thursday__c,'Thursday','')&','&IF(Friday__c,'Friday','')&','&IF(Saturday__c,'Saturday','')&',',",,",","),",,",","),",,",","))-2)