How to split string and copy

**karinos57** · 04-30-2010, 02:09 PM

Hi,
Here is what I am trying to do: I am trying to copy a part of a string and copy it into another columns (Package Size and Package Quantity) but there are 3 scenarios in this problem:
1) NEUPOGEN 480M CG/1.6 VL 10X1.6 ML
2) LOVENOX 40 MG PFS 10
3) COMBIVENT 200 ACT INH 14.7 GM
So let me explain here what I want to do here:
Scenario 1) whenever we have ‘X’, everything to the right of ‘X’ is Package Size and everything to the left of ‘X’ is Quantity
Scenario 2) Whenever the last words in a string are just ‘Numbers’ then that number should be the ‘Package Quantity’ and the ‘Package Size’ is ALWAYS ‘1’
Scenario 3) Whenever we have numbers at the end followed by letters like GM or ML at last, then this number should be Package Size and the Package Quantity is ALWAYS 1.

So this is what the example above should look like:
Package Size Package Quantity
Scenario 1) 1.6 10
Scenario 2) 1 10
Scenario 3) 14.7 1

The code below works but would like to make some changes to reflect the example above and the solution could be in any format as long as it works I am fine with it. Pls. see attached sample file. Thanks for your help.

HTML Code:

**DonkeyOte** · 04-30-2010, 06:10 PM

I confess I'm no expert of RegExp but the below is based on your narrative - the tests don't seem that convoluted that RegExp is an absolute necessity - however - for you to decide of course.

Function ParserTest(rngVal As Range, bType As Byte, Optional strDelim As String = " ")
Dim vVals As Variant, vSubVals As Variant, dblSize As String, lngQty As Long, lngVal As Long
vVals = Split(Application.WorksheetFunction.Trim(rngVal), strDelim)
If Val(vVals(UBound(vVals))) Then
    dblSize = 1
    lngQty = Val(vVals(UBound(vVals)))
Else
    If InStr(vVals(UBound(vVals) - 1), "X") Then
        vSubVals = Split(vVals(UBound(vVals) - 1), "X")
        lngQty = vSubVals(0)
        dblSize = vSubVals(1)
    Else
        Select Case UCase(Right(vVals(UBound(vVals)), 2))
            Case "GM", "ML", "VL"
                lngQty = 1
                dblSize = 1 '? (default if unspecified - eg B13)
                For lngVal = UBound(vVals) To LBound(vVals) Step -1
                    If IsNumeric(vVals(lngVal)) Then
                        dblSize = vVals(lngVal)
                        Exit For
                    End If
                Next lngVal
            Case Else 'unknown ?
                dblSize = 1
                lngQty = 1
        End Select
    End If
End If
ParserTest = IIf(bType = 1, dblSize, lngQty)
End Function

Called as before - eg: =PARSERTEST(B2,1) for Size - 2 for Qty.

Note: I'm not sure I followed all of your expected results but I suspect these nuances may be explained in your RegExp - I confess I've not looked in detail (based above on narrative).