Show individual words from a block of text and count them

Welcome

Assume you have a text string like this in cell A1 (you will need at least one space between each word in the cell):

junk bag junk kind zebra animal

To find the number of words, use this formula:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1
assuming that there is a space between each word.

To list the words, use this formula in cell A4:
=IF(B4="","",MID(LEFT(TRIM($A$1),SEARCH(" ",TRIM($A$1),B3+1)-1),B3+1,100))

and this one in cell B4:
=IF(ISERROR(SEARCH(" ",TRIM($A$1),B3+1)),"",SEARCH(" ",TRIM($A$1),B3+1))

To count how may times each word appears, try this in cell C4:
=IF(A4="","",COUNTIF($A:$A,$A4))

Then copy the formulas down.

I hope that this helps

Is this what you want?

Hi

The above will miss out the last word. Building on this

A4 has
=IF(B4="","",MID(TRIM($A$1),B3+1,B4-B3))
B4 has
=IF(ISERROR(SEARCH(" ",TRIM($A$1),B3+1)),LEN($A$1),SEARCH(" ",TRIM($A$1),B3+1))
C4 has
=IF(A4="","",COUNTIF($A:$A,$A4))

and copy down. Only problem is column B will continue to show a number.


Tony

Re: Show individual words from a block of text and count the

ray: Thank you very much tactps. The code is almost perfect. The only problem I had was that words that are already listed keep on popping up and the one at the end was missing.

For example:

goat frog fish mouse frog hit cat cat cat dog cat
11

goat 5 1
frog 10 2
fish 15 1
mouse 21 1
frog 26 2
hit 30 1
cat 34 3
cat 38 3
cat 42 3
dog 46 1


If you know how to stop the words popping up like cat (3 times) it would be great.





Also thank you very much Tony. Your code was also great and it fixed the last word disapearing problem, but it wasn't counted with the other "cat"s. If either of you know how to fix these problems I would be very happy.

This is what I got when I used your code Tony.


goat frog fish mouse frog hit cat cat cat dog cat


goat 5 1
frog 10 2
fish 15 1
mouse 21 1
frog 26 2
hit 30 1
cat 34 3
cat 38 3
cat 42 3
dog 46 1
cat 49 1
49
49
49
49
49
49
49
49
49

I also tried these formulas with Japanese text and as long as I used English spaces and not Japanese ones the code worked perfectly

Thank you very very much

Show words from a block of text & count them (Glen Rowel

Is there anyone awake now that can help?

Re: Show individual words from a block of text and count the

Have you tried text to columns ?

Re: Show individual words from a block of text and count the

How do I do that? Will it remove the copies from the formula?

Thanks fro the advice

Re: Show individual words from a block of text and count the

data, text to columns.

Wont delete duplicates, but it will split out words into seperate cells so you can work with them easier.

Re: Show individual words from a block of text and count the

That might be very useful but I'm not sure how to use it to get what I want.

What I want to do is keep all the text in the A1 cell because later I will be copying and pasting stories into A1.

Then have all the words and how many times they occur listed.

In the future I will be pasting newspapers into A1 to make a study list of words for Japanese students. This way I can check what they should be studying.

Re: Show individual words from a block of text and count the

Sorry NateO