March 9, 2010

Counting repeated times of single character

We can count a repeated single character in a cell.




In the above example, we have a text value and we have calculated the repeated entries character “O”. The character “O” was entered twice and the result is 2.

The above formula works in three 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,"o","") will remove the character “O”, means we are substituting the value with null and we have counted the number characters in A1 without the “O”, LEN(SUBSTITUTE(A1,"o","")) this formula counted that and finally we have subtracted the result of first formula with the result of second formula and the answer is 2.

1 comment: