How to select a single word from a cell?

[Westman]

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.

 

[JLGWhiz]

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)

 

[ryguy7272]

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

 

[Westman]

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.

 

[Westman]

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.

 

[AltaEgo]

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

 

[JLGWhiz]

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]

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?

 

[Westman]

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]

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.