how to extract multiple words from sentence that have same ending to a new column separated by commas

ruj

New Member
Joined
Nov 20, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
I have an issue whereby for example i have a sentence in column A that says for "hello ID users tbzoo, rjkth, mnkz, plzoo, rtzoo and fghzt" and i would like to create a new column B that extracts all the words in column A that end with zoo and are separated by a comma eg column B would have tbzoo,plzoo,trzoo. So far the formula i have come up with using MID and search only returns the first word that ends in zoo and not all of them. Please help! Thank you
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

You could consider a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down as in B1:B3. The function allows you to set the word ending and also the text to insert between the found words if you want. The default is a comma followed by a space but can be set otherwise as in the B6 formula.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function WordEnds(s As String, wordend 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[^ ]*?" & wordend & "\b"
  For Each itm In RX.Execute(s)
    WordEnds = WordEnds & delim & itm
  Next itm
  WordEnds = Mid(WordEnds, Len(delim) + 1)
End Function

ruj.xlsm
AB
1hello ID users tbzoo, rjkth, mnkz, plzoo, rtzoo and fghzttbzoo, plzoo, rtzoo
2 
3I went to the zoo yesterdayzoo
4
5
6hello ID users tbzoo, rjkzt, mnkz, plzoo, rtzoo and fghztrjkzt-fghzt
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=WordEnds(A1,"zoo")
B6B6=WordEnds(A6,"zt","-")
 

ruj

New Member
Joined
Nov 20, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Thank you soo soo much Peter! It worked perfectly. super grateful for your help here!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,914
Members
416,142
Latest member
IODIEN

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