Finding the whole word from InStr

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
608
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,716
Office Version
  1. 365
Platform
  1. Windows
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

Dr. Demento

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

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
608
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks, Fluff! I didn't see your post before I posted. I like yours better - nice and compact!
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,716
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,857
Office Version
  1. 2013
Platform
  1. Windows
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

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
608
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Also very slick. Thanks Dante.
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,857
Office Version
  1. 2013
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
608
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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,186,988
Messages
5,960,970
Members
438,506
Latest member
KristofGoossens

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
Top