Counting number of entries, separated by a character in a cell

If you would like to count the number of entries in a cell those were separated by a comma, we can take that character “,” as a criteria and count the number of entries.




In the above example, we have names of four persons and it was separated by “,”. We have counted the number of names entered in the cell.

The given formula works in four ways.

1st it would count the total number of characters that exist in the cell A1 using the formula =LEN(A1),

2nd the formula SUBSTITUTE(A1,",","") will remove the character “,”, means we are substituting the value with null and we have counted the number characters in A1 without the “,”, LEN(SUBSTITUTE(A1,"o","")).

3rd we have subtracted the result of first formula with the result of second formula and the answer is 3.

And finally we have added 1 to the above result, since we do not have comma before the first name Rose. The answer is 4.