Excel: Formula for > 1. Listing all unique Words in a Cell Range alphabetically, then > 2. Listing the Total Count for each Word


A B C
1 CELLS WITH TEXT TO BE COUNTED Alphabetical WORD LIST from Column 'A' CELLS WORD COUNT from Column 'A'
2 Test text in here Even 1
3 More test text here Final 1
4 Even more text here here 4
5 Final test text here in 1
6 more 1
7 More 1
8 test 2
10 Test 1
11 text 4
This particular Formula Array request may take some explaining...so please be patient with me as I try to detail the problem. If my explanation is not definitive enough then please ask for clarification. OK, here we go > 1. Cells A2:A5 (this could be an range running up to 1000s of cells in the 'A' column) contains the Text that needs to be searched (each cell could contain up to 100 words that will exclusively contain letters - no numbers, symbols or punctuation). 2. I'm looking for a Formula that can search all of the Words in the 'A' column Range, and then list each unique word (case sensitive) as shown in the 'B' column, in alphabetical order. 3. The 'C' column will provide the Word Count for each Word listed in column 'B'. NOTE: I have tried taking the Words in column 'A' and using the "Data/Text to Columns" command to put each word occurrence in a different cell, and then using the "Filter" command to list each column in alphabetical order, and finally using the "=COUNT" command to total the "Filter" list, but this is too cumbersome and time consuming. There must be a more efficient way of doing this. Thanks.


This question generated 32 answers. To proceed to the answers, click here.

This thread is current as of October 27, 2013.


For more resources for Microsoft Excel