Parsing based on content

[Rose]

I need to parse 1 column into 3 columns, but it isn't
based on length or string position, nor by commas etc.
It's going to be based on content. For instance:
THERMO HYDROX LEM/LIM 20OZ 24/
is a product. If I see the text "LEM" (or one of several
other strings), I need a parse there. Same at 20OZ, a
value that will always begin with a number and have OZ,
CAPS, or TABS following.
I'm sure this is possible, but heck if I can figure it
out! If we were talking about tens of thousands of
records eventually, I wouldn't ask.

 

[shockley]

Rose,

You can use the Instr function to test for a string within a string, eg.,

If Instr(1, sString, "LEM", vbBinaryCompare) Then 'Parse

Or, you can use the LIKE operator, as this allows you to test using wildcard
characters. For instance, if you know that there will always be two numbers
preceding "OZ", you could use the statement

If sString Like "*##OZ*" Then 'Parse...

Or, perhaps you know that there will be 2 or 3 numbers preceding "OZ":

If sString Like "*##OZ*" Or sString Like "*###OZ*" Then 'Parse...

If you use the Like operator, check VBEditor Help! for how to use the Option
Compare statement to make the comparison case sensitive or not. Same goes
for the Instr function.

HTH,
Shockley

 

[Rose Cox]

Thanks for the reply! But I need a little more handholding than that.

Here's what I've got:
Private Sub LEM_split()
If InStr(1, sString, "LEM", vbBinaryCompare) Then
Worksheets("tblWebProducts").Columns("C").Parse _
parseLine:="sString", _
Destination:=Worksheets("tblWebproducts").Range("D1")
Else
End If
End Sub

(tblWebProducts is my Sheet1)

VBEditor says it's valid, but it doesn't work. Obviously I missed
something. Is there a reason this shouldn't work if my text says LEMON
instead of just LEM?

Again, some typical lines of text might include:
BALANCE BAR LEMON + CAL.15/BX
PRO COSANOID 600MG 60CAP
GROW VANILLA 3LB


I'll need to parse the flavor (if it exists) and forward into a
different column, and then run a similar sub on both columns to see if I
find ## and then BX, TABS, or one of another packaging type, which will
go into a 3rd column. I assume I'll need to add Else If lines for each
string I want to check for (VAN,KIWI,CHOC for the first sub, ##/BX,
##CAPS, ##TABS, #LB for the second).

Thanks again!

 

[shockley]

How about something like:



Option Compare Text
Sub Tester()
arrPackTerms = Array("##/BX", "##CAP", "##TABS", "#LB")
arrFlavTerms = Array("VANILLA", "KIWI", "CHOCOLATE", "LEMON")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
s = Cells(i, 1)

For j = LBound(arrFlavTerms) To UBound(arrFlavTerms)
sParseTerm = arrFlavTerms(j)
If InStr(1, s, sParseTerm, vbTextCompare) > 0 Then
Cells(i, 2) = sParseTerm
Exit For
End If
Next j

For j = LBound(arrPackTerms) To UBound(arrPackTerms)
sParseTerm = arrPackTerms(j)
If s Like "*" & sParseTerm & "*" Then
Cells(i, 3) = sParseTerm
Exit For
End If
Next j
Next i
End Sub

 

[Rose Cox]

Great - that separates out the flavor name. Thanks!

However, when it finds 2.34 LB, it's writing #LB into the cell because
that's the array value (therefore sParseTerm). How do we get it to
actually find numbers (and possibly a period as above) and the "LB" and
write that whole part of the cell value into the other column the way
it's doing for the flavor?

And while we're at it, we also need to end the loop by deleting the
values we parsed out from the original cell. It's not s=s-sParseTerm
MID doesn't work for this. Are we going to have to find the Left
character of the parsed string and trim from there? And if so, how??

 

[shockley]

That would be something like this <smug grin>:


Option Compare Text
Sub Tester()
StopChar = " "
arrPackTerms = Array("/BX", "CAP", "TABS", "LB")
arrFlavTerms = Array("VANILLA", "KIWI", "CHOCOLATE", "LEMON")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
s = Cells(i, 1)

For j = LBound(arrFlavTerms) To UBound(arrFlavTerms)
sParseTerm = arrFlavTerms(j)
TermPos = InStr(1, s, sParseTerm, vbTextCompare)
If TermPos > 0 Then
Cells(i, 2) = sParseTerm
s = Left(s, TermPos - 2) & Mid(s, TermPos + Len(sParseTerm))
Cells(i, 1) = s
Exit For
End If
Next j

For j = LBound(arrPackTerms) To UBound(arrPackTerms)
sParseTerm = arrPackTerms(j)
TermPos = InStr(1, s, sParseTerm, vbTextCompare)
If TermPos > 0 Then
s1 = sParseTerm
x = 0
Do
x = x + 1
sChar = Mid(s, TermPos - x, 1)
If sChar <> StopChar Then s1 = sChar & s1
Loop Until sChar = StopChar

s1Pos = InStr(1, s, s1, vbTextCompare)
s = Trim(Left(s, s1Pos - 1) & Mid(s, s1Pos + Len(s1)))
Cells(i, 1) = s
Cells(i, 3) = s1
Exit For
End If
Next j
Next i
End Sub