| 0 |
Subject: Finding Text In Excel
Posted by: MadAndRabidDOG
- [412552721] Mon, Mar 28, 2005, 14:25
Anybody know of an easy way to look for certain words in a text string and if the word is found to put something (a number or text) in the cell that I'm working in.
I would have probably a long list of words that I'm looking for maybe 100 words. If any of these words were found in any text strings in a column of about 500 rows with text strings in them I would like to mark the cell that I'm in.
I have tried using functions like Exact, Search, Find, etc. It's not easy at least to me. Any help would be appreciated. |
| 1 | MadAndRabidDOG
ID: 412552721
Mon, Mar 28, 2005, 14:28
|
The key word in my post is "Easy". I have a very difficult way of doing it. I'm looking for a simple way.
Most likely someway of using a Lookup function for this. Remember there is going to be alot of words maybe 100.
|
| 2 | MadAndRabidDOG
ID: 412552721
Tue, Apr 05, 2005, 14:09
|
Does anybody know if there is a way to do this. Say I
have a list of words
Car
Apple
Fan
House
Fork
Say I have a text string in one cell such as "My car is
in the house in my garage".
I want to put a formula into the cell next to this text
string and then do the following.
What I need is some sort of function or formula that will
look at my list of words and tell me which words on the
list are in the text string and then mark the cell with
what words it found.
In this example in my text string are the words "House"
and "Car", so I would want the formula to put into the
cell an "H" telling me it found the word "House" and
a "C" telling me it found the word "Car"
So the final result in my cell where the formula is would
be "HC".
Is this possible? Boy, if you could help me with this it
would be greatly appreciated. Thank You.
I posted that in a Microsoft Excel Newsgroup. Still waiting for an answer.
|
| 3 | Astade
Donor
ID: 214361313
Tue, Apr 05, 2005, 21:46
|
Don't know why I am helping you, but here goes ;)
If you are not familiar with macros I don't think it's worth learning for a simple task like this. If you need to do this over and over again (ie daily) then I suggest you learn a little vb and programatically solve this.
For a quick fix:
In this case you have 5 key words you are searching for.
Take all the strings you are going to search and put them into column A starting on row 1. In column B, insert this line-
=COUNTIF(G9,"*car*")>0
now propagate that formula down the rows for all strings. For each instance where 'car' is found a value of TRUE should display. Column B is your instances of car. Now just copy Column B and do a paste special where you paste just the values to Column C. Now you have captured the results. Do a find and replace for that column and you'll have 'c' in each row where the string 'car' was found. Store that column somewhere else (another worksheet?) and get started on the next word. Repeating the procedure should give you 5 columns of results. To combine your results to get 'hc' all you need to do is a string concatenation which is straight forward.
In column F (the 6th column which should be blank)insert:
=concatenate(a1,b1,c1,d1,e1)
Then you should be golden.
Let me know if it works out for you. It's pretty easy and intuitive. If I was cryptic post back here and I'll help ya.
|
| 4 | MadAndRabidDOG
ID: 412552721
Tue, Apr 05, 2005, 22:19
|
Astade I need to do this daily and automatically.
I will be pasting new text strings in this spreadsheet daily and I want the formula or functions to find my list of words automatically and mark a column if they are found.
Some guy from Sweden who answered my post at Microsoft newsgroups created a function for me that seems like it might work. I'm going to give it a try.
|
| 5 | MadAndRabidDOG
ID: 412552721
Tue, Apr 05, 2005, 22:21
|
BTW, I think you're right it has to be solved with VB.
Which I happen to know nothing about. Maybe this guys function will work.
|
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|