ok.

--


Gary Keramidas
Excel 2003


"Ron Rosenfeld" wrote in message
...
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