Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm having the following set of data:
enter image description here

A simple COUNTA would yield n=4. Using the above-mentioned formula will give also n=4.

The answer needs to be however n=6: Jacob Jan (1x), Jacob (2x), Jan (3x)

How can I count the number of occurrences of each word in column A?

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

Hm, that's an interesting question. If you're looking for just the n of terms in the array, you can do this without splitting the terms.

=SUM(ARRAYFORMULA(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4, ",", "")) + 1))

Alright, here's what's happening in this formula.

  • In A1, we take the len() of the string and subtract the len() of the string after substituting the comma. In this case we get 9 - 9 = 0. We then add 1 to get 1 (the number of terms in the cell)
  • We can use arrayformula() on this to apply the same subtraction function to every cell in the given range (in this case A1:A4)
  • The final piece is to sum() the numbers from the resulting range and get the total # of terms that are split on the comma.

Let's look at cell A2 to see a case of when the comma is used. We take len(A2) which is 10 and subtract len(A2) after substituting the comma for a null character "" which is 9. We get 10 - 9 = 1 then we add 1 to the total to get 2 which is equal to the number of terms in the cell.

Hope this helps!

share|improve this answer
1  
You make it sound easy. I've googled around, tried myself, but this is the first working example I've seen. Great work. –  Jacob Jan Tuinstra Jan 9 '13 at 22:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.