PDA

View Full Version : Removing parts before each item



swaggerbox
02-10-2020, 04:13 AM
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
02-10-2020, 05:35 AM
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
02-10-2020, 05:41 AM
wow, just amazing Paul. Thank you

snb
02-10-2020, 07:35 AM
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
02-12-2020, 03:55 AM
An alternative path, thanks snb.