Wednesday, September 07, 2005

Counting Commas or Special characters in a cell

Suppose, your names seperated by "," is in cell A4.

Here is the formula to calculate number of commas in the cell A4:

=LEN(A4)-LEN(SUBSTITUTE(A4,",",""))


The first part: LEN(A4) calculates length of string with commas.


The Second part: LEN(SUBSTITUTE(A4,",","")) calculates length of string without commas.

The differnece of the two i.e. first part-second part will give you number of commas in your string.

This formula can come out handy in many calculations.