Finding the whole word from InStr

[Dr. Demento]

Could someone point me in the direction of a function that once InStr finds a substring, the function returns the entire word? For example, fx_WholeWord("I went to the theater and someone must have turned on the heater", "heat") would return both "theater" and "heater"

Thanks y'all.

 

[Fluff]

How about

Function DrDemento(St As String, Srch As String) As String
   Dim Sp As Variant
   Dim i As Long
   
   Sp = Split(St)
   For i = 0 To UBound(Sp) - 1
      If InStr(1, Sp(i), Srch, vbTextCompare) > 0 Then
         DrDemento = DrDemento & Sp(i) & ", "
      End If
   Next i
   If InStr(1, Sp(i), Srch, vbTextCompare) > 0 Then DrDemento = DrDemento & Sp(i)
End Function

Used like
=DrDemento(A2,"heat")

 

[Dr. Demento]

I think I figured it out. If anyone can see any glaring errors, I'd appreciate the feedback.

Function InStr_EntireWord(rav As String, _
                         ravTerm As String) As String
                      
Dim arrStr As Variant
  arrStr = Splitrav, " ")
  
Dim cntr As Long, _
    iSeek As Long

Dim str As String

  iSeek = InStr(1, rav, ravTerm, vbTextCompare) ' ~~ Partial match \ Case INsensitive
    
  Do While iSeek > 0
    cntr = 0
    For i = LBound(arrStr) To UBound(arrStr)
      cntr = cntr + Len(arrStr(i)) + 1
      If cntr >= iSeek Then
        cntr = i
        Exit For
      End If
    Next i
    
    str = str & "|" & arrStr(cntr)
    iSeek = InStr(iSeek + 1, rav, ravTerm, vbTextCompare)
  Loop
  
  InStr_EntireWord = str
  
End Function

 

[Dr. Demento]

Thanks, Fluff! I didn't see your post before I posted. I like yours better - nice and compact!

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

Try this

Function testx(r As Range, txt As String)
    Dim elem As Variant
    For Each elem In Split(r, " ")
        If elem Like "*" & txt & "*" Then testx = testx & elem & ", "
    Next
    If testx <> "" Then testx = Left(testx, Len(testx) - 2)
End Function

 

[Dr. Demento]

Also very slick. Thanks Dante.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Dr. Demento]

I just ran across an instance where I'm using a search phrase instead of a single term; in the interest of future inquiries here's my solution.

My thought was to look for a single space in the search term and if found, concatenate the first instance found in the string with the next term as well. Using Fluff's example, it would look like

If InStr(1, Sp(i), Srch, vbTextCompare) > 0 Then
     If InStr(Srch, " ") > 0 Then 
         DrDemento = DrDemento & Sp(i) & Sp(i + 1) & ", "
     Else
         DrDemento = DrDemento & Sp(i) & ", "
     End If
End If

If the search phrase has more than one space, you would have to insert a loop to concatenate; alternatively, you could use the Srch phrase, although that defeats the idea of finding how the whole phrase exists in the string. For example, the search phrase could be "I could" but the phrase in the string is "I couldn't." Reporting just the search phrase would omit how the phrase is actually found in the string.