Show individual words from a block of text and count them

[Glen Charles Rowel]

Does anyone know how to write a formula that lists all the words from one cell alphabetically, and counts how many times they occur?

I want to find out which English words are used the most to help people learn English in Japan.



If you can write formulas, I just want to copy and past something.

Here is a cool formula for your help To figure out how old you are.

By the way, I'm new at this. So, I'm just learning by copying and pasting.


1922/12/12

=DATEDIF(A4,TODAY(),"y")&" years, "&DATEDIF(A4,TODAY(),"ym")&" months & "&DATEDIF(A4,TODAY(),"md")&" days."

 

[Glen Charles Rowel]

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

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


5 goat #VALUE!
10 frog #VALUE!
15 fish #VALUE!
21 mouse #VALUE!
26 frog FALSE
30 hit #VALUE!
34 cat #VALUE!
38 cat FALSE
42 cat FALSE
46 dog #VALUE!
cat FALSE
#VALUE!
FALSE


Something went wrong.


Do I have to put the info in a special order
A1 first
then D1
and after E4...

 

[Yogi Anand]

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

Hi Glen Charles Rowel:

Let us have a look at ...

y041012h1.xls
	A	B	C	D	E	F	G	H
1	goatfrogfishmousefroghitcatcatcatdogcat			6			11
2
3	word_in_cell_A1	word_count		0	word_in_cell_A1	word_count
4	goat	1		1	goat	1	5
5	frog	2		2	frog	2	10
6	fish	1		3	fish	1	15	ColumnsD:Gare
7	mouse	1		4	mouse	1	21	Helper
8	hit	1		0	frog	2	26	columns
9	cat	4		5	hit	1	30
10				6	cat	4	34
11				0	cat	4	38
12				0	cat	4	42
13				7	dog	1	46
14					cat	4
15
Sheet2 (3)

Are we close enough yet?

 

[Glen Charles Rowel]

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

That is fantastic

Now I can check the word lists I have

There is only one other problem. If I insert text into the A1 cell only the first sentence works.

For example:



A1= This topic has received a reply since your last visit. You can use the following link to view the replies made, no more notifications will be sent until you visit the topic.
7 32


This 1 1 This 1 5
topic 1 2 topic 1 11
has 1 3 has 1 15
received 1 4 received 1 24
a 1 5 a 1 26
reply 1 6 reply 1 32
since 1 7 since 1 38
8 your 1 43
9 last 1 48
10 visit. 1 55
11 You 2 59
12 can 1 63
13 use 1 67
14 the 3 71
15 following 1 81
16 link 1 86
17 to 1 89
18 view 1 94
0 the 3 98
19 replies 1 106
20 made, 1 112
21 no 1 115
22 more 1 120
23 notifications 1 134
24 will 1 139
25 be 1 142
26 sent 1 147
27 until 1 153
0 you 2 157
28 visit 1 163
0 the 3 167
29 "topic.
" 1
30
0
0
0
0
0






Only the first sentence is counted and I don't want the full-stops (periods), quotation marks and commas shown

 

[Glen Charles Rowel]

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

I changed the number after D

And now all the words get displayed

I just need help removing the full-stops (periods), qoutation marks and commas.

Thank you so much everyone


A1=This topic has received a reply since your last visit. You can use the following link to view the replies made, no more notifications will be sent until you visit the topic

D1=MAX(D4:D100)

G1=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

A4=IF(ROWS($1:1)>$D$1,"",INDEX($E$4:$E$100,MATCH(ROWS($1:1),$D$4:$D$100,0)))

B4=IF(ROWS($1:1)>$D$1,"",INDEX($F$4:$F$100,MATCH(ROWS($1:1),$D$4:$D$100,0)))

D4=IF(COUNTIF($E$4:$E4,E4)=1,1+MAX($D$3:$D3),0)

E4=IF(ROWS($1:1)>$G$1,"",IF(ROWS($1:1)=$G$1,MID($A$1,$G3+1,255),IF(G4="","",MID(LEFT(TRIM($A$1),SEARCH(" ",TRIM($A$1),$G3+1)-1),$G3+1,100))))

F4=IF(E4="","",COUNTIF($E:$E,E4))

G4=IF(ISERROR(SEARCH(" ",TRIM($A$1),G3+1)),"",SEARCH(" ",TRIM($A$1),G3+1))

 

[Glen Charles Rowel]

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

I want to put the text in a cell and then have the full-stops, quotation marks, at-signs, etc removed. Could someone tell me how to do this?

Thanks in advance :wink:

 

[Glen Charles Rowel]

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

A1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,",",""),".",""),")",""),"(",""),"0","")

B1=original text "gchvghvjhv" jgjhgjh. hgjh jhgjh hgjh gjhg word word word etc

Using this code I can get rid of all but the quotation marks.
,".","") gets rid of a .
but ,""","") doesn't gets rid of the " mark. How do I do this?

 

[Yogi Anand]

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

Hi Glen Charles Rowel:

Let us have a look at ...

Book1
	A	B	C	D
1	RemovingtheQuoteMarks
2		Glen--"GoodGuy"--CharlesRowel	Glen--GoodGuy--CharlesRowel
3
Sheet8

Would this do?

 

[Paul M]

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

Hello Everyone:

Is there a way to do the list of multiple cells with each cell containing a bunch of text, then have one list output?

Thanks

 

[Yogi Anand]

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

Hello Everyone:

Is there a way to do the list of multiple cells with each cell containing a bunch of text, then have one list output?

Thanks

Hi Paul:

It will help if you would post some sample data and your expected result(s)!