Extract every words, after certain character, in any position within a cell. Then split

[superjoejoe]

Need help to


1. Extract every words, after "Type>", in any position within a cell.
2. Split the results into 2 columns, after cross checked with existing list

Hope these could be solved in 1 step.
Please see attachment.


Thank you


Also posted here Extract every words, after certain character, in any position within a cell. Then split

 

[DanteAmor]

Assuming your data as follows.

varios 06jul2020.xlsm
	A	B	C	D	E
1					List
2	Chrismas, Festive, Outerwear	Outerwear	Chrismas, Festive		Top
3	Chinese New Year, Sets	Sets	Chinese New Year		Outerwear
4	Shoes	Shoes			Sets
5					Shoes
6					and so on
7
8
9
Hoja4

Try this:

Sub test()
  Dim c As Range, rList As Range, f As Range
  Dim sWord As Variant, cad1 As String, cad2 As String
  
  Set rList = Range("E2", Range("E" & Rows.Count).End(3))
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    cad1 = ""
    cad2 = ""
    For Each sWord In Split(c, ",")
      Set f = rList.Find(Trim(sWord), , xlValues, xlWhole)
      If Not f Is Nothing Then
        cad1 = cad1 & Trim(sWord) & ", "
      Else
        cad2 = cad2 & Trim(sWord) & ", "
      End If
    Next sWord
    If cad1 <> "" Then c.Offset(, 1).Value = Left(cad1, Len(cad1) - 2)
    If cad2 <> "" Then c.Offset(, 2).Value = Left(cad2, Len(cad2) - 2)
  Next c
End Sub