Hi all,
How to select a single word from a cell in Excel 2007?

Example: I like to eat chicken rice at the restaurant.
The statement is placed in a single cell. So, if I highlight the "like"
word, how do I get it using vb codes? Thanks.

ps: I'm using VsTO, visual studio 2005, excel 2007.

Assume the phrase is in cell B2.

Sub sk()
extr = Mid(Range("B2").Value, InStr(Range("B2"), "like"), 4)
MsgBox extr
End Sub

Or since you know the phrase, you could simply count the characters,
including the spaces.

extr = Mid(Range("B2").Value, 3 , 4)


"Westman" wrote in message
...
Hi all,
How to select a single word from a cell in Excel 2007?

Example: I like to eat chicken rice at the restaurant.
The statement is placed in a single cell. So, if I highlight the "like"
word, how do I get it using vb codes? Thanks.

ps: I'm using VsTO, visual studio 2005, excel 2007.

Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer


myword = InputBox("Enter the search string ")

Mylen = Len(myword)
Set rng = Selection
For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JLGWhiz" wrote:

Assume the phrase is in cell B2.

Sub sk()
extr = Mid(Range("B2").Value, InStr(Range("B2"), "like"), 4)
MsgBox extr
End Sub

Or since you know the phrase, you could simply count the characters,
including the spaces.

extr = Mid(Range("B2").Value, 3 , 4)


"Westman" wrote in message
...
Hi all,
How to select a single word from a cell in Excel 2007?

Example: I like to eat chicken rice at the restaurant.
The statement is placed in a single cell. So, if I highlight the "like"
word, how do I get it using vb codes? Thanks.

ps: I'm using VsTO, visual studio 2005, excel 2007.

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex = 3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the current
active cell. Not the highlighted word. With the above codes, all words will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in thesaurus
function (Review - Thesaurus), I'm able to get the highlighted word(just a
single word) from any phrase in any cell.



"ryguy7272" wrote:

Sub Highlight_Word()
Dim rng As Range
Dim cell As Range
Dim start_str As Integer


myword = InputBox("Enter the search string ")

Mylen = Len(myword)
Set rng = Selection
For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, Mylen).Font.ColorIndex = 3
End If
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JLGWhiz" wrote:

Assume the phrase is in cell B2.

Sub sk()
extr = Mid(Range("B2").Value, InStr(Range("B2"), "like"), 4)
MsgBox extr
End Sub

Or since you know the phrase, you could simply count the characters,
including the spaces.

extr = Mid(Range("B2").Value, 3 , 4)


"Westman" wrote in message
...
Hi all,
How to select a single word from a cell in Excel 2007?

Example: I like to eat chicken rice at the restaurant.
The statement is placed in a single cell. So, if I highlight the "like"
word, how do I get it using vb codes? Thanks.

ps: I'm using VsTO, visual studio 2005, excel 2007.

Sorry, forgot one thing. How to know what is the current selection in excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex = 3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the current
active cell. Not the highlighted word. With the above codes, all words will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in thesaurus
function (Review - Thesaurus), I'm able to get the highlighted word(just a
single word) from any phrase in any cell.

If you are trying to click on a word in the formula bar and have VBA select
it and use it, I suspect VBA cannot do this. You can select the single word
of you choice, copy (using Ctrl-C), press Enter or Escape and the word will
be available to paste. Once you have it stored in the clipboard, you can get
it into a VB variable from the clipboard if you set a reference to
Microsoft Forms 2.0 Object Library (instructions within the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the clipboard
from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex = 3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the current
active cell. Not the highlighted word. With the above codes, all words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted word(just
a
single word) from any phrase in any cell.

You are correct AltaEgo, I should have read the OP post closer. I thought
it a little odd that someone would want code to select a word from a
pharse. Finding a word in a phrase would make more sense. Or finding a
phrase that contained the word.
Unfortunately, selecting part of a cell's content and then trying to run a
macro with it just is not compatible with Excel.


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you are trying to click on a word in the formula bar and have VBA
select it and use it, I suspect VBA cannot do this. You can select the
single word of you choice, copy (using Ctrl-C), press Enter or Escape and
the word will be available to paste. Once you have it stored in the
clipboard, you can get it into a VB variable from the clipboard if you
set a reference to Microsoft Forms 2.0 Object Library (instructions within
the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the
clipboard from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex =
3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the current
active cell. Not the highlighted word. With the above codes, all words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted
word(just a
single word) from any phrase in any cell.

Perhaps if the OP can explain in a little more detail, someone can offer a
more elegant solution than copy from formula bar then take the value from
the clipboard.

BTW did you see Liliana's message that Colo is back?

--
Steve

"JLGWhiz" wrote in message
...
You are correct AltaEgo, I should have read the OP post closer. I thought
it a little odd that someone would want code to select a word from a
pharse. Finding a word in a phrase would make more sense. Or finding a
phrase that contained the word.
Unfortunately, selecting part of a cell's content and then trying to run a
macro with it just is not compatible with Excel.


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you are trying to click on a word in the formula bar and have VBA
select it and use it, I suspect VBA cannot do this. You can select the
single word of you choice, copy (using Ctrl-C), press Enter or Escape and
the word will be available to paste. Once you have it stored in the
clipboard, you can get it into a VB variable from the clipboard if you
set a reference to Microsoft Forms 2.0 Object Library (instructions
within the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the
clipboard from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex =
3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the
current
active cell. Not the highlighted word. With the above codes, all words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted
word(just a
single word) from any phrase in any cell.

So does it mean it's not possible to "duplicate" the excel built-in thesaurus
function in my own code?

Actually, I want to create my own thesaurus using my country locale
language. So far, the only problem remaining is "selecting a specific word"
and then replace its synonym. I've successfully completed the process in MS
Word, PowerPoint, Outlook and InfoPath. For Infopath, I've to use clipboard
method to get the word. But Excel doesn't give/show me what is the current
selection and now I' stuck at this point...

"AltaEgo" wrote:

Perhaps if the OP can explain in a little more detail, someone can offer a
more elegant solution than copy from formula bar then take the value from
the clipboard.

BTW did you see Liliana's message that Colo is back?

--
Steve

"JLGWhiz" wrote in message
...
You are correct AltaEgo, I should have read the OP post closer. I thought
it a little odd that someone would want code to select a word from a
pharse. Finding a word in a phrase would make more sense. Or finding a
phrase that contained the word.
Unfortunately, selecting part of a cell's content and then trying to run a
macro with it just is not compatible with Excel.


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you are trying to click on a word in the formula bar and have VBA
select it and use it, I suspect VBA cannot do this. You can select the
single word of you choice, copy (using Ctrl-C), press Enter or Escape and
the word will be available to paste. Once you have it stored in the
clipboard, you can get it into a VB variable from the clipboard if you
set a reference to Microsoft Forms 2.0 Object Library (instructions
within the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the
clipboard from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng =
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword, vbTextCompare)
If start_str Then
cell.Characters(start_str, MyLen).Font.ColorIndex =
3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the
current
active cell. Not the highlighted word. With the above codes, all words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted
word(just a
single word) from any phrase in any cell.

The add-in on this page may help:
http://excelusergroup.org/media/p/4263.aspx

Note that the author states Word should be installed on the PC, indicating
that possibly the easiest solution would be to copy your entire cell content
to a Word session and use the code you already built in Word.

--
Steve

"Westman" wrote in message
...
So does it mean it's not possible to "duplicate" the excel built-in
thesaurus
function in my own code?

Actually, I want to create my own thesaurus using my country locale
language. So far, the only problem remaining is "selecting a specific
word"
and then replace its synonym. I've successfully completed the process in
MS
Word, PowerPoint, Outlook and InfoPath. For Infopath, I've to use
clipboard
method to get the word. But Excel doesn't give/show me what is the current
selection and now I' stuck at this point...

"AltaEgo" wrote:

Perhaps if the OP can explain in a little more detail, someone can offer
a
more elegant solution than copy from formula bar then take the value from
the clipboard.

BTW did you see Liliana's message that Colo is back?

--
Steve

"JLGWhiz" wrote in message
...
You are correct AltaEgo, I should have read the OP post closer. I
thought
it a little odd that someone would want code to select a word from a
pharse. Finding a word in a phrase would make more sense. Or finding
a
phrase that contained the word.
Unfortunately, selecting part of a cell's content and then trying to
run a
macro with it just is not compatible with Excel.


"AltaEgo" <Somewhere@NotHere wrote in message
...
If you are trying to click on a word in the formula bar and have VBA
select it and use it, I suspect VBA cannot do this. You can select the
single word of you choice, copy (using Ctrl-C), press Enter or Escape
and
the word will be available to paste. Once you have it stored in the
clipboard, you can get it into a VB variable from the clipboard if
you
set a reference to Microsoft Forms 2.0 Object Library (instructions
within the code below).

Code from : http://www.cpearson.com/excel/Clipboard.aspx

You may do some further reading here to find out how to clear the
clipboard from Excel.


Sub test()

'In VBA module click
'Tools, References
'Check Microsoft Forms 2.0 Object Library

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
MsgBox S

End Sub

HTH
--
Steve

"Westman" wrote in message
...
Sorry, forgot one thing. How to know what is the current selection in
excel?
because I don't use any input box in my codes.

"Westman" wrote:

Thanks for reply, JLGWhiz and Ryan.

I'm sorry, but I don't quite understand the codes. When you set rng
=
Selection, are you refering to Application.Selection?

Dim rng As Range
Dim cell As Range
Dim start_str As Integer
Dim myword As String
Dim MyLen As Integer

myword = InputBox("Enter the search string ")

MyLen = Len(myword)
rng = Globals.ThisAddIn.Application.Selection

For Each cell In rng
start_str = InStr(1, cell.Value, myword,
vbTextCompare)
If start_str Then
cell.Characters(start_str,
MyLen).Font.ColorIndex =
3
keyword = cell.Characters(start_str, MyLen).Text
MessageBox.Show(keyword)
End If
Next

When I use Application.Selection, it will select all text in the
current
active cell. Not the highlighted word. With the above codes, all
words
will
change to red colour.

to JLGWhiz:
Just to clarify, the phrase could be anything, and the word that I
want
to
look for are not predefined.

Maybe for a simpler explanation, in Excel 2007, using the built-in
thesaurus
function (Review - Thesaurus), I'm able to get the highlighted
word(just a
single word) from any phrase in any cell.