Thursday, May 17, 2012

Excel Quick Tip: Counting occurrences of a character in a cell

I found an interesting problem recently. We have a shared google docs at work for folks to sign up for hobby clubs. The idea was people to sign up against clubs by adding their names in a cell.

In the next week or so, the docs was filled with names and I wondered if there was a cool way to count the people who had signed up, using a simple formulae.

So here is the solution, which works both in excel and google docs.

Since each name is separated by a single comma (,)  the total number of people signed up is the count of Commas in a cell + 1.

In the above example, I want to count the , in column B. In the next cell, type this formula

C2  =LEN(B2)-LEN(SUBSTITUTE(B2,",","")) + 1

Let me explain how this works. As the name suggests, Substitute replaces a certain text/character ( in our case ,)within a string, with something else. Trick is to replace it with empty string (notice nothing between the last double quotes), and count length of strings before and after. So if there are 5 commas, Substitute returns a string 5 characters shorter, and that's what we want. Pretty easy right!

Hope you learnt something new today. Happy number crunching!

No comments: