Text - find text?

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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
ABCDEFGHIJK
1ParaNo.Statementquickbrownfoxredhenhousefarmerdog
21Thequickbrownfoxjumpedovertheredhenhouse.XXXXXX
32Thehenwasafraidwhenthathappened.X
43Thefarmersdogwentafterthefox.XXX
5
Sheet3

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

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

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

Code:
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
ABCD
1ParaNo.Statementquickbrown
221the quick brown fox jumped over the lazy dogs1
332The brown hen liked chocolate because it's brown2
...
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
dk - can the function be made to work if the heading contains two words, "brown fox" and count that as one occurence?
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

ADVERTISEMENT

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
ABCDE
21ParaNo.Statementquickbrown fox
3221the quick brown fox jumped over the lazy dogsX
4332The brown hen liked chocolate because it's brown 
...
 

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
Hi em.

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

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


Regards Sir Vili.
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
dk and Sir Vili - thanks, will try both when i get to work ...
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
dk - that worked perfect ...

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

Forum statistics

Threads
1,144,329
Messages
5,723,733
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top