Extract multiple words from sentence that have same beginning

Lcorreia

New Member
Joined
Apr 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, I know this is an old thread, but I'm having the same issue, but instead of looking for the end of the string, I'm searching the start of the string and returning the full word. For example, hello ID zooTb, zoort, tzift, searching for zoo and returning zooTb, zoort. Thank you :)
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Welcome to the MrExcel board!

So best to start your own thread so that it shows up in the Unanswered threads list as that usually gets more helpers looking at your question.
You can add a link to an old thread if it is relevant. In this case I have moved your question and added the link for you.

See if this adaptation of my function from that previous thread is what you want.

VBA Code:
Function WordStarts(s As String, wordstart As String, Optional delim As String = ", ") As String
  Dim RX As Object, itm As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b" & wordstart & ".*?\b"
  For Each itm In RX.Execute(s)
    WordStarts = WordStarts & delim & itm
  Next itm
  WordStarts = Mid(WordStarts, Len(delim) + 1)
End Function

Lcorreia.xlsm
AB
1hello ID zooTb, zoort,tziftzooTb, zoort
Sheet1
Cell Formulas
RangeFormula
B1B1=WordStarts(A1,"zoo")
 
Upvote 0
Another option, with a formula
+Fluff 1.xlsm
AB
1zoo
2hello ID zooTb, zoort, tziftzooTb, zoort
Main
Cell Formulas
RangeFormula
B2B2=TEXTJOIN(", ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,","," ")," ","</m><m>")&"</m></k>","//m[starts-with(.,'"&B$1&"')]"))


This is case sensitive, so won't find words that begin Zoo, ZOO etc
 
Upvote 0
Welcome to the MrExcel board!

So best to start your own thread so that it shows up in the Unanswered threads list as that usually gets more helpers looking at your question.
You can add a link to an old thread if it is relevant. In this case I have moved your question and added the link for you.

See if this adaptation of my function from that previous thread is what you want.

VBA Code:
Function WordStarts(s As String, wordstart As String, Optional delim As String = ", ") As String
  Dim RX As Object, itm As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b" & wordstart & ".*?\b"
  For Each itm In RX.Execute(s)
    WordStarts = WordStarts & delim & itm
  Next itm
  WordStarts = Mid(WordStarts, Len(delim) + 1)
End Function

Lcorreia.xlsm
AB
1hello ID zooTb, zoort,tziftzooTb, zoort
Sheet1
Cell Formulas
RangeFormula
B1B1=WordStarts(A1,"zoo")
Thank you for the advice, next time I will start a new thread.

About the code, it kinda works for me, my search word have a hiffen for example: 1ABC123456-1234, and when applying your code it only returns the string before the hiffen.
 
Upvote 0
Another option, with a formula
+Fluff 1.xlsm
AB
1zoo
2hello ID zooTb, zoort, tziftzooTb, zoort
Main
Cell Formulas
RangeFormula
B2B2=TEXTJOIN(", ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,","," ")," ","</m><m>")&"</m></k>","//m[starts-with(.,'"&B$1&"')]"))


This is case sensitive, so won't find words that begin Zoo, ZOO etc
This works, but if it seems that it isn't admitting a Paragraph as the end of the string, for example:

TextReturns
hello ID zooTb, zoort, tzift, zoofg
some other text
zooTb, zoort, zoofgsome

I think this, "(...) [starts-with(.,'"&B$1&"')]"))", might be the part I would have to change but I'm not sure, what should I add
 
Upvote 0
How about
Excel Formula:
=TEXTJOIN(", ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,","," "),CHAR(10)," ")," ","</m><m>")&"</m></k>","//m[starts-with(.,'"&B$1&"')]"))
 
Upvote 0
Solution
How about
Excel Formula:
=TEXTJOIN(", ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,","," "),CHAR(10)," ")," ","</m><m>")&"</m></k>","//m[starts-with(.,'"&B$1&"')]"))
Yes this works, thank you very much
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
What could I add so that when it does not find anything it returns an empty cell instead of #VALUE! error? I've been with if conditions and I'm not managing to figure it out
 
Upvote 0
You can wrap it in iferror like
Excel Formula:
=IFERROR(TEXTJOIN(", ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,","," "),CHAR(10)," ")," ","</m><m>")&"</m></k>","//m[starts-with(.,'"&B$1&"')]")),"")
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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