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 have a Google Spreadsheet with a single column that holds string values (Twitter screen names) such as "user1", "user1", "UserX", and I would like to count those values so that I can easily craft a bar chart out of it. So the result should be

value        occurrence
-----------------------
user1         2
UserX         1
...          ....

Please note, I only want to look for whole words, and not part words. For example, the words 'on' and 'one' appears in the word 'money' - I would not count this (i.e., only the word 'money' is counted).

Hope that is clear enough. What formula should I use?

share|improve this question

migrated from superuser.com Dec 14 '12 at 14:54

This question came from our site for computer enthusiasts and power users.

    
Did you want to find words within words though? EG, if your spreadsheet only consisted of 1 word, which was blabla, do you want to see the results as shown in your post? –  Dave Rook Dec 14 '12 at 9:25
    
No, no words within words, maybe the example is abit confusing. "bla" and "blabla" are just two different values, could be "bla" and "blu", for example. –  wnstnsmth Dec 14 '12 at 9:29
    
And lastly, what about capitalization - does that matter. How good are you with VBa? And are all the words just 1 word, or some 2 or 3 words long (sentences) –  Dave Rook Dec 14 '12 at 9:31
    
Sorry, as per my VBa comment, is it an option to export to Excel? Otherwise, try this link developers.google.com/apps-script/overview –  Dave Rook Dec 14 '12 at 9:38
1  
If you do spreadsheets often, I recommend learning a bit of scripting. Just a little can go a long way. Learning curve typically is hard at first until you get going but persevere :) –  Dave Rook Dec 14 '12 at 9:50

1 Answer 1

up vote 6 down vote accepted

In Google Spreadsheet:

  1. Prepare all unique items: =UNIQUE(A1:A10)
  2. Count them: =COUNTIF($A$1:$A$10,B1)
  3. Added them together: =CONCATENATE(B1," ",C1)

See example I've prepared: Find the number of occurrence of a word

share|improve this answer

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.