Find how many times a comma occurs in a text field

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered
I have tried using the Count function as in Count(",",[text field]) however it always returns 2, even if there is only 1 comma.
Photo of Steven

Steven

  • 40 Points

Posted 2 years ago

  • 0
  • 2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Count() counts the number of non-null arguments not the number of characters in an argument.


You have to use JavaScript to do this because the formula language lacks any iterative or looping mechanism.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
If there is a reasonable maximum number of commas you can do this.

if(part([my field],1,",")<>""),1,0) +

if(part([my field],2,",")<>""),1,0) +


if(part([my field],3,",")<>""),1,0) +

if(part([my field],4,",")<>""),1,0) 




But that will actually count the ocurrences data between the commas and not the commas themselves.  Are you really trying to count comas or the parts between the commas.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Yes this is the best than can be done with native - you have to hardcode a maximum number of instances you want to process. The formulas become long and if everyone does this a lot performance slowly suffers.