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
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!
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:
Post a Comment