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

Hit Ctrl+F.From Find window click on 'Options'. Make sure you check 'Match
entire cell contents' and then do the find.

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hit Ctrl+F.From Find window click on 'Options'. Make sure you check 'Match
entire cell contents' and then do the find.

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

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

Sorry, it cannot work. It says: "Microsoft Office Excel cannot find the data
you're searching for." Is there any way?

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hit Ctrl+F.From Find window click on 'Options'. Make sure you check 'Match
entire cell contents' and then do the find.

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

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

On Fri, 27 Nov 2009 23:51:01 -0800, Jack
wrote:

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

How is your dictionary structured?
--ron

If you are searching for a word match like 'vision' uncheck 'Match entire
cell contents' and find ' vision 'FindAll... which will list down cell
references which is having a full word 'vision' in between sentences.

'with 'Match Case' checked
'Vision '

'sentences ending with vision.
' vision.'

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

Sorry, it cannot work. It says: "Microsoft Office Excel cannot find the data
you're searching for." Is there any way?

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hit Ctrl+F.From Find window click on 'Options'. Make sure you check 'Match
entire cell contents' and then do the find.

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

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

Still no help. It still finds words containing 'vision', like division, etc.
Well, I now understand that excel does not have such a function. Anyway,
thank you for your help, Jacob Skaria.

"Jacob Skaria" wrote:

If you are searching for a word match like 'vision' uncheck 'Match entire
cell contents' and find ' vision 'FindAll... which will list down cell
references which is having a full word 'vision' in between sentences.

'with 'Match Case' checked
'Vision '

'sentences ending with vision.
' vision.'

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

Sorry, it cannot work. It says: "Microsoft Office Excel cannot find the data
you're searching for." Is there any way?

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hit Ctrl+F.From Find window click on 'Options'. Make sure you check 'Match
entire cell contents' and then do the find.

If this post helps click Yes
---------------
Jacob Skaria


"Jack" wrote:

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

Very simple. I arrange Mongolian words (I am from Mongolia) in column A and
their English translation in column B. So far, I have 12693 rows, which make
me hard to find short and repeated words, like profit, air, vehicle, etc.

"Ron Rosenfeld" wrote:

On Fri, 27 Nov 2009 23:51:01 -0800, Jack
wrote:

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

How is your dictionary structured?
--ron
.

The following macro will do what you want; just change the assignments in
the two Const (constant) statements at the beginning of the code to the
worksheet name and column letter that want to search in. If you want, you
can attach the macro to a button (either on the worksheet or the toolbar) to
make accessing it easier. The macro will ask you for the word or phrase you
want to find (letter casing is not important) and then locate it if it
exists in the list (or tell you that the word or phrase could not be
found)... after finding the first occurrence of the word or phrase, you will
be given the opportunity to look for its next occurrence in case the first
occurrence was not the one you wanted. This continued searching will not end
until you click the No button on the dialog box that asks you about
continuing the search.

Sub FindExactWord()
Dim C As Range, FindWord As String, Answer As Long
Const SearchColumn As String = "B"
Const WorksheetName As String = "Sheet1"
FindWord = InputBox("What word do you want to find?")
With Worksheets(WorksheetName)
Set C = .Columns(SearchColumn).Find(FindWord, LookIn:=xlValues, _
LookAt:=xlPart, After:=Cells(.Rows.Count, "B"), _
MatchCase:=False, SearchDirection:=xlNext)
If Not C Is Nothing Then
Do
If " " & UCase(C.Value) & " " Like "*[!A-Z]" & _
UCase(FindWord) & "[!A-Z]*" Then
C.Select
Answer = MsgBox("Continue searching?", vbQuestion Or vbYesNo)
If Answer = vbNo Then Exit Do
End If
Set C = .Columns(SearchColumn).FindNext(C)
Loop
Else
MsgBox "I could not find """ & FindWord & """."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Jack" wrote in message
...
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

On Sat, 28 Nov 2009 07:24:01 -0800, Jack
wrote:

Very simple. I arrange Mongolian words (I am from Mongolia) in column A and
their English translation in column B. So far, I have 12693 rows, which make
me hard to find short and repeated words, like profit, air, vehicle, etc.

Will a word appear by itself? In other words, if you are looking for

vision

do you also want to find cells that contain, for example:

vision; sightings

??

--ron

Here is a slight modification to my macro so that the FindWord surrounded by
numbers is not considered a stand-alone word...

Sub FindExactWord()
Dim c As Range, FindWord As String, Answer As Long
Const SearchColumn As String = "B"
Const WorksheetName As String = "Sheet1"
FindWord = InputBox("What word do you want to find?")
With Worksheets(WorksheetName)
Set c = .Columns(SearchColumn).Find(FindWord, LookIn:=xlValues, _
LookAt:=xlPart, After:=Cells(.Rows.Count, "B"), _
MatchCase:=False, SearchDirection:=xlNext)
If Not c Is Nothing Then
Do
If " " & UCase(c.Value) & " " Like "*[!A-Z]" & _
UCase(FindWord) & "[!A-Z0-9]*" Then
c.Select
Answer = MsgBox("Continue searching?", vbQuestion Or vbYesNo)
If Answer = vbNo Then Exit Do
End If
Set c = .Columns(SearchColumn).FindNext(c)
Loop
Else
MsgBox "I could not find """ & FindWord & """."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
The following macro will do what you want; just change the assignments in
the two Const (constant) statements at the beginning of the code to the
worksheet name and column letter that want to search in. If you want, you
can attach the macro to a button (either on the worksheet or the toolbar)
to make accessing it easier. The macro will ask you for the word or phrase
you want to find (letter casing is not important) and then locate it if it
exists in the list (or tell you that the word or phrase could not be
found)... after finding the first occurrence of the word or phrase, you
will be given the opportunity to look for its next occurrence in case the
first occurrence was not the one you wanted. This continued searching will
not end until you click the No button on the dialog box that asks you
about continuing the search.

Sub FindExactWord()
Dim C As Range, FindWord As String, Answer As Long
Const SearchColumn As String = "B"
Const WorksheetName As String = "Sheet1"
FindWord = InputBox("What word do you want to find?")
With Worksheets(WorksheetName)
Set C = .Columns(SearchColumn).Find(FindWord, LookIn:=xlValues, _
LookAt:=xlPart, After:=Cells(.Rows.Count, "B"), _
MatchCase:=False, SearchDirection:=xlNext)
If Not C Is Nothing Then
Do
If " " & UCase(C.Value) & " " Like "*[!A-Z]" & _
UCase(FindWord) & "[!A-Z]*" Then
C.Select
Answer = MsgBox("Continue searching?", vbQuestion Or vbYesNo)
If Answer = vbNo Then Exit Do
End If
Set C = .Columns(SearchColumn).FindNext(C)
Loop
Else
MsgBox "I could not find """ & FindWord & """."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Jack" wrote in message
...
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

That's right. For example, I have 'vision' in the following cells (rows):
- vision statement
- hallucination of vision
- to lose one's vision as he/she ages
- field of vision, etc.

In order to find just 'vision,' I have to search through the following cells
(rows), wasting time:
- Geology and Mining Cadastre Division (many other cells that contain
'division')
- divisional commander, divisional structure
- political subdivision
- administrative supervision
- provision
- television
- provisional invoice, and so many words.

I hope you understand my problem from the above.

"Ron Rosenfeld" wrote:

On Sat, 28 Nov 2009 07:24:01 -0800, Jack
wrote:

Very simple. I arrange Mongolian words (I am from Mongolia) in column A and
their English translation in column B. So far, I have 12693 rows, which make
me hard to find short and repeated words, like profit, air, vehicle, etc.

Will a word appear by itself? In other words, if you are looking for

vision

do you also want to find cells that contain, for example:

vision; sightings

??

--ron
.

On Sat, 28 Nov 2009 11:38:02 -0800, Jack
wrote:

Wonderful It does work. Thanks a lot for your help.

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

On Sat, 28 Nov 2009 14:52:36 -0500, "Rick Rothstein"
wrote:

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?

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.

"Ron Rosenfeld" wrote:

On Sat, 28 Nov 2009 11:38:02 -0800, Jack
wrote:

Wonderful It does work. Thanks a lot for your help.

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
.

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.

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.

--
Rick (MVP - Excel)

On Nov 29, 8:31*pm, Ron Rosenfeld wrote:
On Sat, 28 Nov 2009 20:32:01 -0800, Jack
wrote:

Well, your revision did not work for me, but the first option was great. It
works without any problem.

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

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

On Tue, 1 Dec 2009 19:53:56 -0800 (PST), minyeh wrote:

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

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

On Dec 2, 12:21*pm, Ron Rosenfeld wrote:
On Tue, 1 Dec 2009 19:53:56 -0800 (PST), minyeh wrote:
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

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 -

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)

On Dec 2, 1:41*pm, minyeh wrote:
On Dec 2, 12:21*pm, Ron Rosenfeld wrote:





On Tue, 1 Dec 2009 19:53:56 -0800 (PST), minyeh wrote:
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

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 -

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)- Hide quoted text -

- Show quoted text -

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

On Tue, 1 Dec 2009 21:41:57 -0800 (PST), minyeh wrote:

On Dec 2, 12:21*pm, Ron Rosenfeld wrote:
On Tue, 1 Dec 2009 19:53:56 -0800 (PST), minyeh wrote:
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

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 -

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)

You're getting there, but also indicating why using VBA and/or Regular
Expressions is much simpler.

This is a [vision] -- FALSE

--ron