5

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?

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
10

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

0

A pivot table (under Data) should handle this requirement very well:

WA36643 example

Applying default settings the control total and alphabetical sorting are automatic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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