Removing parts before each item

[swaggerbox]

Suppose I have a text string like these:

Scenario 1:
5-15 parts of bean pulp, 50.5 parts of rapeseed meal, 20-100 parts of peanut, 30 parts of corn

Scenario 2:
5-15 pts. wt. bean pulp, 50.5 pts. wt. rapeseed meal, 20-100 pts. wt. peanut, 30 pts. wt. corn

Scenario 3:
5-15 weight% bean pulp, 50.5 weight% rapeseed meal, 20-100 weight% peanut, 30 weight% corn

How to I remove the parts (and their values) and leave the string with only the items, like this one?

Desired output:
bean pulp, rapeseed meal, peanut, corn

[Paul_Hossler]

probably not 100% bullet proof

Option Explicit


Sub drv()
    Dim s1 As String, s2 As String, s3 As String


    s1 = "5-15 parts of bean pulp, 50.5 parts of rapeseed meal, 20-100 parts of peanut, 30 parts of corn"
    s2 = "5-15 pts. wt. bean pulp, 50.5 pts. wt. rapeseed meal, 20-100 pts. wt. peanut, 30 pts. wt. corn"
    s3 = "5-15 weight% bean pulp, 50.5 weight% rapeseed meal, 20-100 weight% peanut, 30 weight% corn"
    
    'bean pulp, rapeseed meal, peanut, corn
    MsgBox JustItems(s1)
    MsgBox JustItems(s2)
    MsgBox JustItems(s3)
    
End Sub




Function JustItems(s As String) As String
    Dim s1 As String
    Dim v As Variant
    Dim i As Long
    
    v = Split(s, " ")


    For i = LBound(v) To UBound(v)
        If v(i) Like "*[0-9]*" Then
            v(i) = vbNullString
        ElseIf LCase(v(i)) = "of" Then
            v(i) = vbNullString
        ElseIf LCase(v(i)) = "of" Then
            v(i) = vbNullString
        ElseIf InStr(v(i), "part") Then
            v(i) = vbNullString
        ElseIf InStr(v(i), "pt") Then
            v(i) = vbNullString
        ElseIf InStr(v(i), "weight") Then
            v(i) = vbNullString
        ElseIf InStr(v(i), "wt") Then
            v(i) = vbNullString
        End If
    Next i
    
    s1 = Join(v, " ")
    
    JustItems = Replace(s1, "  ", " ")
End Function

[swaggerbox]

wow, just amazing Paul. Thank you

[snb]

Sub snb()
  s1 = "5-15 parts of bean pulp, 50.5 parts of rapeseed meal, 20-100 parts of peanut, 30 parts of corn"
  s2 = "5-15 pts. wt. bean pulp, 50.5 pts. wt. rapeseed metal, 20-100 pts. wt. peanut, 30 pts. wt. corn"
  s3 = "5-15 weight% bean pulp, 50.5 weight% rapeseed meal, 20-100 weight% peanut, 30 weight% coorn"
    
  sn = Split("pulp_rapeseed meal_peanut_corn", "_")

  For j = 1 To 3
    s4 = Choose(j, s1, s2, s3)
    MsgBox Join(Filter(Array(IIf(InStr(s4, sn(0)), sn(0), "~"), IIf(InStr(s4, sn(1)), sn(1), "~"), IIf(InStr(s4, sn(2)), sn(2), ""), IIf(InStr(s4, sn(3)), sn(3), "~")), "~", 0), "_")
  Next
End Sub

[swaggerbox]

An alternative path, thanks snb.