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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.