Identify a single word amongst many within a cell
 

Hi all

Can anyone explain how I can identify a word in cell, whether in uppe
or lower case, and display that word in another cell please? The wor
is is part of a group of words.

Many thanks, in anticipation

--
micol
-----------------------------------------------------------------------
micolo's Profile: http://www.excelforum.com/member.php...fo&userid=3310
View this thread: http://www.excelforum.com/showthread.php?threadid=52965

Identify a single word amongst many within a cell
 

One way ... looks for "fox" in A1 and places in B1 if found

textTofind = "fox"
n = InStr(1, Range("A1"), textTofind, vbTextCompare)
If n 0 Then
Range("B1") = Mid(Range("B1"), n, Len(textTofind))
End If

HTH

"micolo" wrote:


Hi all

Can anyone explain how I can identify a word in cell, whether in upper
or lower case, and display that word in another cell please? The word
is is part of a group of words.

Many thanks, in anticipation.


--
micolo
------------------------------------------------------------------------
micolo's Profile: http://www.excelforum.com/member.php...o&userid=33102
View this thread: http://www.excelforum.com/showthread...hreadid=529653

Identify a single word amongst many within a cell
 

Hi Micolo,

Try this:

Sub SplitString()

Dim oCell As Range
Dim sMySearchWord As String
Dim itm As Variant

sMySearchWord = "Cat"
Set oCell = Application.ActiveCell
For Each itm In Split(oCell.Value)
If UCase(itm) = UCase(sMySearchWord) Then
oCell.Offset(0, 5).Value = itm
End If
Next itm

End Sub

Best regards

John


"micolo" wrote in
message ...

Hi all

Can anyone explain how I can identify a word in cell, whether in upper
or lower case, and display that word in another cell please? The word
is is part of a group of words.

Many thanks, in anticipation.


--
micolo
------------------------------------------------------------------------
micolo's Profile:
http://www.excelforum.com/member.php...o&userid=33102
View this thread: http://www.excelforum.com/showthread...hreadid=529653

Identify a single word amongst many within a cell
 

or something like this with an inputbox



Sub find_it()
Dim AName As String
Dim rng As Range, rngToSearch As Range
AName = Application.Proper(Application.InputBox("Enter A Name", _
Title:="Find Name", Type:=2))

With Worksheets("Sheet1")
.Unprotect
Set rngToSearch = .Range("C3:C20")
Set rng = rngToSearch.Find(What:=AName, LookAt:=xlPart)
If Not rng Is Nothing Then
' your code
MsgBox AName & " found"

Else
' your code
MsgBox AName & " not found"
End If
End With
End Sub

--


Gary


"micolo" wrote in message
...

Hi all

Can anyone explain how I can identify a word in cell, whether in upper
or lower case, and display that word in another cell please? The word
is is part of a group of words.

Many thanks, in anticipation.


--
micolo
------------------------------------------------------------------------
micolo's Profile:
http://www.excelforum.com/member.php...o&userid=33102
View this thread: http://www.excelforum.com/showthread...hreadid=529653

Identify a single word amongst many within a cell
 

Thank you

:) John, Toppers and Gary I'll give the solutions a try and see ho
they work.

micol

--
micol
-----------------------------------------------------------------------
micolo's Profile: http://www.excelforum.com/member.php...fo&userid=3310
View this thread: http://www.excelforum.com/showthread.php?threadid=52965

Identify a single word amongst many within a cell
 

Sorry folks I hope you'll pardon my ignorance, but can you talk me
through this step by step i.e. exactly what to do?

Thanks


--
micolo
------------------------------------------------------------------------
micolo's Profile: http://www.excelforum.com/member.php...o&userid=33102
View this thread: http://www.excelforum.com/showthread...hreadid=529653