On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas"
wrote:

ron:

would something like this be simpler than using regex? i know it's powerful,
but i have a hard time understanding it.

just wondering

Sub test()
Dim lastQt As Long
Dim lastrow As Long
Dim ws As Worksheet
Dim cell As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In ws.Range("A1:A" & lastrow)
lastQt = InStrRev(cell.Value, """")
If lastQt 0 Then
cell.Offset(, 1).Value = Trim(Left(cell.Value, lastQt))
cell.Offset(, 2).Value = Trim(Right(cell.Value, Len(cell.Value) _
- lastQt))
End If
Next
End Sub

Your approach would probably run faster. But "simpler" is in the eye of the
beholder. It's pretty simple for me to devise and test a regex, so I save
"development" time. Especially if the initially given parameters are
incomplete, as is frequently the case, and require modifications.

For example, we don't know how the OP wants to handle entries that do NOT have
measurements. I chose to put the non-measurement part in the same column as
the other part descriptions; you ignore it; p45cal has it in the measurements
column.

For me to change the treatment of that instance requires only a small change in
the regex pattern.

By the way, given the OP's requirements, your lastQt line should probably be:

lastQt = WorksheetFunction.Max(InStrRev(cell.Value, """"), _
InStrRev(cell.Value, "'"))

--ron