Calculated field to count number of comma separated values entered in Single line text field

[truptikhairnar]

Hello, I need help in creating a calculated field which should give me the count of values entered in single line text field separated by comma.

E.G - I have created below single line field ABC = 111,234,32,456,777

So, here there are 5 terms/values entered in above field which are separated by comma.

But, I need calculated field which will show "5" number .

Please help me, how can I get this count using calculated field.

[KellieGardner]

To my knowledge doing a count in Workfront is limited. It's not a custom data expression available. 

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/calculated-custom-data/cal...

[truptikhairnar]

Yes, we dont have any option available for this case.

[skyehansen]

hi, you could try this (I got it years ago, from one of the professional services folks) and see if it works.

You can see that it only counts up to 8 (it looks like anything over 8 also gets called 8), so it might not be a good solution for you.

Hopefully you can see why it's not a sustainable solution! As long as you are expecting a small number of commas and no more, it could be workable.

Since I got this several years ago, there has been a change in how calculated fields are now constructed in Workfront. I've made a good faith attempt putting in the DE's and the curly braces, but since I've never had the need to use the calculated expression, you will have to test it in your particular situation.

Comma Counting
This calculation counts the number of commas in a field, but stops counting after 8

IF(ISBLANK({DE:CommaContainingField}),"0",IF(SEARCH(",",{DE:CommaContainingField})<0,"0",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)<0,"1",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)<0,"2",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)<0,"3",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)<0,"4",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)<0,"5",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)+1)<0,"6",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)+1)+1)<0,"7","8")))))))))

[truptikhairnar]

Thanks for reply. But, unfortunately, it will not work for me as the values will be more than 8 and max count is upto 100.

[Doug_Den_Hoed__AtAppStore · Accepted solution]

Hi @truptikhairnar (and @skyehansen),

I noodled around on this today, and provided you do not use any commas within the values of your multi-select, invite you to use the following formula on a calculated parameter (e.g. Multi Select Count) to calculate the number of selected values in a target multi-select parameter (e.g. Multi Select Parm), which will support up to 100 values (or even more) with no additional maintenance required:

IF(LEN({DE:Multi Select Parm})>0,1+LEN(REPLACE({DE:Multi Select Parm},",",",,"))-LEN({DE:Multi Select Parm}),0)

Thanks for the cool challenge, and enjoy!

Regards,

Doug

TIP: if this solved your problem, I invite you to consider marking it as a Correct Answer to help others who might also find it of use

[truptikhairnar]

Thanks Doug.