How to count the entries in a single cell in each row?

[#1]

Hello All,

Below is the example I am working with. "Column-to-Count" has several entries in each cell within that column. I want to output the count of each cell, which is the "Count" column.

|    Column-to-Count    |    Count    |

|    ab1, tgh9o, pl79s    |    3             |

|    ab1, fisc8u               |    2             |

|    ab1, psivb, 7ushg8 |    3             |

Is there a formula type that I can use to generate the "Count" column? I looked at the "count" formula type and that did not seem to be what I need.

Thank You!

[#2 · Accepted solution]

Here is the formula

Length( Words( :"Column-to-Count"n, "," ) )

[#3]

Excellent - this works great - thank you!

@txnelson follow-up question, what if there are multiple "ab" items and I just want to count those? 

|    Column-to-Count                    |     ab Count    |

|    ab1, ab2, ab3, tgh9o, pl79s    |          3             |

|    ab1, fisc8u                               |          1             |

|    ab1, psivb, 7ushg8                 |          1             |

I tried to use the formula above with Length( Words( :"Column-to-Count"n, "ab" ) ) and it did not give me the correct count.

[#4]

Here's a good way to do that.  I use Trim() to eliminate leading whitespace and Starts With() to make sure I don't count entries that just happen to contain "ab" somewhere.  Filter Each() requires JMP 16+.

Length( Filter Each( {v, i}, Words( :"Column-to-Count"n, "," ), Starts With( Trim( v ), "ab" ) ) )

[#5]

Another way

New Table( "Sample table",
	Add Rows( 3 ),
	New Column( "Column-to-count",
		Character,
		Nominal,
		Set Values( {"ab1, tgh9o, pl79s", "ab1, fisc8u", "ab1, psivb, 7ushg8"})
	),
	New Column("Count",
	Numeric,
	Continuous,
	Formula(N Items( Words( :"Column-to-count"n, ", " ) ))
		
	)
)

[#6]

To count only elements that have "ab", here's a brute force formula

alist = Words( :"Column-to-count"n, ", " );
acount = 0;
For( i = 1, i <= N Items( alist ), i++,
	If( Contains( alist[i], "ab" ),
		acount
		++)
);
acount;