Extracting group using keyword

excel_1

New Member
Joined
Jul 12, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Have data in columnA and trying to filter data using keywords. member of groups is in the down adjacent cells. starting with {space} or just a cell without space.

Rich (BB code):
tom1.0 jerry mickey mouse
tom jerry2/1
mickey3+4 mouse
  tom
  jerry
  mickey
  Mouse
mickey mouse tom jerry2/1
mickey mouse
  tom1.0 jerry
  tom jerry
  tom
  jerry
mouse-x mickey
  mouse mickey
  tom jerry2/1 mickey


VBA Code:
Sub Mymacro()
    Range("B2:B2000").Clear
    For Each Cell In Sheets(1).Range("A1:A2000")
        matchrow = Cell.Row
        Find = "*" + Worksheets("Sheet1").Range("B1") + "*"
        If Cell.Value Like Find Then
        Cell.Offset(0, 1).Value = Cell.Offset(0, 0).Value
        End If
    Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        
        Call Mymacro
    End If
End Sub

The above code is extracting text correctly but only if group is starting without {space}. if the group is multiline. only partial matching can be extracted.
some examples on expected o/p.
 

Attachments

  • exl_test.png
    exl_test.png
    56.5 KB · Views: 17

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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