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

Forum|Forum|2 years ago
May 2, 2023
3 replies
1965 views

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.

Workfront

Best answer by Doug_Den_Hoed__AtAppStore

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

T

truptikhairnarAuthor

New Participant

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__AtAppStoreAccepted solution

New Participant

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

T

truptikhairnarAuthor

New Participant

Thanks Doug.

skyehansen

New Participant

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")))))))))