Formula for listing all unique Words in a Cell Range

[johnetico]

Hey Guys, I am in need for some Help.

I have a workbook with a lot of text and words in different cells, all in one column. There are hundreds of cells, all in one column, and each cell has many different words. I need to extract each word from this cells, and list them sepparately and individually: One word per cell, all in one column, and i don´t know how to do that!

After that I need to count how many appearences each word has, but I know how to do that with formulas, once I have the list extracted from the original column.

I found this thread, which seems to solve all my problems, but it is old, and it is made for windows, so I cannot make it work:

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...

www.mrexcel.com

I appreciate if someone can help me with this, either with formulas or VBA. If it is VBA, I appreciate if you can help me out with a step-by-step for mac, since I am not really familiar with VBA or macros.

Thanks a lot!

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some samples of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kweaver]

I suspect you'll need VBA for this (which might not work on a MAC) until the TEXTSPLIT (or whatever it'll be called) is released (about 40 years behind most programming languages).

 

[Fluff]

It's doable with a formula, but it depends on what the data is like.