Finding the whole word from InStr

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
How about
Code:
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")
 
Upvote 0
I think I figured it out. If anyone can see any glaring errors, I'd appreciate the feedback.

Code:
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
 
Upvote 0
Thanks, Fluff! I didn't see your post before I posted. I like yours better - nice and compact!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Try this


Code:
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top