Identify a single word amongst many within a cell

[micolo]

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

 

[Guest]

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

 

[John]

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

 

[Gary Keramidas]

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

 

[micolo]

Thank you

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

micol

 

[micolo]

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