Text - find text?

[itr674]

I am trying to re-write a manual and want to analyze how often certain words or phrases are used so I can see if I can consolidate the manual. I want to have the words or phrases as column headings and an X would be placed in that column. After I run the code or "in cell formuals" I would filter that column by the "X" and then I could see exactly where the term is used and how often.

Question - would it be better to use VBA or is there a formula that could be used in a cell that would place an X in the column of the particular word or phrase used? Also, may need to use wildcard character in some places.

I started this project yesterday by coloring words so I could put the X in manually, but I keep finding that I am missing words, so think now that automation is more accurate ...

GunsFlow.xls
	A	B	C	D	E	F	G	H	I	J	K
1	ParaNo.	Statement	quick	brown	fox	red	hen	house	farmer	dog
2	1	Thequickbrownfoxjumpedovertheredhenhouse.	X	X	X	X	X	X
3	2	Thehenwasafraidwhenthathappened.					X
4	3	Thefarmersdogwentafterthefox.			X				X	X
5
Sheet3

This message was edited by em on 2002-10-30 11:55

 

[dk]

Hi,

You could possibly use this custom function. It counts the number of times a word appears in a specified sentence.

Function CountWords(Sentence, WordToFind)
    Dim lng As Long
    Dim lngWordCount As Long
    Sentence = Sentence.Value
    vArray = Split(Sentence, " ")
    For lng = 0 To UBound(vArray)
        If UCase$(WordToFind) = UCase$(vArray(lng)) Then lngWordCount = lngWordCount + 1
    Next lng
    CountWords = lngWordCount
End Function

So you may use it like this:-

Book12
	A	B	C	D
1	ParaNo.	Statement	quick	brown
2	2	1	the quick brown fox jumped over the lazy dogs	1
3	3	2	The brown hen liked chocolate because it's brown	2
...

 

[itr674]

dk - OK, just tested it in one cell and it works. I am going to plug in all cells and see what the data looks like.

The only problem I can see is filtering because instead of having one "X" I may end up with a 1, 2, 3 etc so filtering might not work right.

Also, do you think a wildcard character would work in the function.

This is first time I've used a function like this and it's pretty neat ...

 

[itr674]

dk - can the function be made to work if the heading contains two words, "brown fox" and count that as one occurence?

 

[dk]

Hi,

If you just want an X to show up and you want to search for a string (one or more words) then you could use Excel's SEARCH function. E.g.

=SEARCH("brown fox","The brown fox ate the rabbit")

will look for 'brown fox' in the sentence 'The brown fox ate the rabbit'. If it finds it, it returns the number of the character where the matched text is found - in this case 5. If it doesn't find anything it returns the #VALUE error. You could implement something like this:-

Book1
	A	B	C	D	E
2	1	ParaNo.	Statement	quick	brown fox
3	2	2	1	the quick brown fox jumped over the lazy dogs	X
4	3	3	2	The brown hen liked chocolate because it's brown
...

 

[Sir Vili]

Hi em.

If I understand correctly Your problem, use this formula in C2:

=IF(ISNUMBER(SEARCH(C$1,$B2)),"X","")


Regards Sir Vili.

 

[itr674]

dk and Sir Vili - thanks, will try both when i get to work ...

 

[itr674]

dk - that worked perfect ...

Sir Vili - I will try your solution later today ...