How to find a particular word?

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hello, everybody!

I have a dictionary that I created in excel and that is becoming too large
to find particular words. I have one problem with finding. I want to find,
for example, 'vision,' but it finds all words containing 'vision,' i.e.
division, divisional, visionary, provision, etc. What I need is that I want
to find only the word 'vision' in order not to waste time, finding unrelated
words, as mentioned above.

Is there any solution to it? Please help me.

Thanks in advance.
Jack
 
If you are looking for a word match within a cell then add a space at the
beginning and end
Find what: ' Vision '


If this post helps click Yes
 
Wonderful It does work. Thanks a lot for your help.
Click to expand...

You're welcome. Glad to help. Thanks for the feedback.

This message seems as if you are responding to my original macro post. If that
is the case, please see my revision which gracefully handles the instance where
the word (or phrase) being searched for is not found at all.

--ron
 
Ron's macro works fine, so I'm glad you found a solution that works for you;
but I'm curious... can you see my response to your original question which
contains code that should also work for you? I posted it at least a
half-hour before Ron asked you for clarification on what you were trying to
search for and see no follow ups to it by you, so I'm just wondering if you
can even see my original message to you or not?
Click to expand...

It's interesting. I can see your macro, and your revision. But in other
threads, in this and other MS newsgroups, which I access via the
msnews.microsoft.com news server, I know that I don't see some messages. I can
tell this by seeing people responding to messages which I never see, but can
sometimes find accessing the NG using a different method.

One functional difference between yours and mine, is that your search range is
limited to a single column, whereas mine encompasses the entire sheet. Of
course, either could be changed if the OP found it necessary.
--ron
 
Well, your revision did not work for me, but the first option was great. It
works without any problem.
 
One functional difference between yours and mine, is that your search
range is
limited to a single column, whereas mine encompasses the entire sheet. Of
course, either could be changed if the OP found it necessary.
Click to expand...

The only reason I restricted my search to a single column is because in the
OP first response to you he indicated that the words he wanted to search for
were in Column B. I'm guessing he doesn't have words in other columns that
he could "accidentally" find using your code. As you said, though, either of
our routines can be modified to handle whatever range is needed to be
searched in.
 
Well, your revision did not work for me, but the first option was great. It
works without any problem.
Click to expand...

Something is strange about that. The only difference is a bit of code to
handle the instance where the word you are searching for is not present.

The additional lines are about 15 lines up from the bottom:

========================
If i = 0 Then
MsgBox (" """ & w & """ not found")
Exit Sub
End If
=========================

Are you certain you copied the revision correctly?
What does "did not work" mean? What happens with a "found" word and when you
use a word that is not present?
--ron
 
Something is strange about that.  The only difference is a bit of code to
handle the instance where the word you are searching for is not present.

The additional lines are about 15 lines up from the bottom:

========================
If i = 0 Then
   MsgBox (" """ & w & """ not found")
   Exit Sub
End If
=========================

Are you certain you copied the revision correctly?
What does "did not work" mean?  What happens with a "found" word and when you
use a word that is not present?
--ron
Click to expand...

My approach would be simply adding a conditional formatting on the
column B you want to lookup for:

1. highlight column B,
2. conditional formatting,
3. new rule> use a formula to determine which cell to format
4. =OR(LEFT($B2,6)=$C$1,ISNUMBER(FIND(" "&$C$1,$B2)))
* where i assume B1 is the heading and data starts from B2 onward
* and $C$1 is the referrence for the word "vision", u can key-in any
word in the cell for lookup
5. format the cell as u like
* would suggest to fill with colours, 'cos excel07 can filter by
coloured cell.

that's all
 
My approach would be simply adding a conditional formatting on the
column B you want to lookup for:

1. highlight column B,
2. conditional formatting,
3. new rule> use a formula to determine which cell to format
4. =OR(LEFT($B2,6)=$C$1,ISNUMBER(FIND(" "&$C$1,$B2)))
* where i assume B1 is the heading and data starts from B2 onward
* and $C$1 is the referrence for the word "vision", u can key-in any
word in the cell for lookup
5. format the cell as u like
* would suggest to fill with colours, 'cos excel07 can filter by
coloured cell.

that's all
Click to expand...

Your formula returns

Visionary --> TRUE
- to lose one's "vision" as he/she ages --> FALSE

And, of course, the option to sort by color won't work in earlier versions of
Excel; and scrolling 10,000+ rows for each word can get cumbersome.
--ron
 
Your formula returns

Visionary --> TRUE
- to lose one's "vision" as he/she ages --> FALSE

And, of course, the option to sort by color won't work in earlier versions of
Excel; and scrolling 10,000+ rows for each word can get cumbersome.
--ron- Hide quoted text -

- Show quoted text -
Click to expand...

ya, that's my problem i think,
i ammended the formula, this should work,

=IF(ISNUMBER(FIND($C$1,B2)),AND(IF(FIND($C$1,B2)=1,TRUE,OR(CODE(MID
(B2,FIND($C$1,B2)-1,1))<65,CODE(MID(B2,FIND($C$1,B2)-1,1))>122)),IF
(FIND($C$1,B2)+LEN($C$1)>LEN(B2),TRUE,OR(CODE(MID(B2,FIND($C$1,B2)+LEN
($C$1),1))<65,CODE(MID(B2,FIND($C$1,B2)+LEN($C$1),1))>122))),FALSE)
 
ya, that's my problem i think,
i ammended the formula, this should work,

=IF(ISNUMBER(FIND($C$1,B2)),AND(IF(FIND($C$1,B2)=1,TRUE,OR(CODE(MID
(B2,FIND($C$1,B2)-1,1))<65,CODE(MID(B2,FIND($C$1,B2)-1,1))>122)),IF
(FIND($C$1,B2)+LEN($C$1)>LEN(B2),TRUE,OR(CODE(MID(B2,FIND($C$1,B2)+LEN
($C$1),1))<65,CODE(MID(B2,FIND($C$1,B2)+LEN($C$1),1))>122))),FALSE)- Hidequoted text -

- Show quoted text -
Click to expand...

and ya, in fact u can use the same formula on custom filter. so u
don't need to scroll over 10,000+ rows
 
Back
Top