If you have xl2000 or later

Sub WriteData()
j = 0
set rng = Range(Cells(2,1),Cells(2,1).End(xldown)
set rng1 = selection.parent.next.Range("A2")
for each cell in rng
sID = cell.Value
v = Split(cell.offset(0,1).Value,",")
for i = lbound(v) to ubound(v)
rng1.offset(j,0).Value = sID
rng1.offset(j,1).Value = "'" & left(v(i),len(v(i))-1)
rng1.offset(j,2).Value = Right(v(i),1)
j = j + 1
Next i
Next cell
End Sub

Select the sheet with you numbers as the active sheet. The results are
written to the next sheet in the tab order (so this should be blank).

It starts in A2 of each sheet (adjust to suit). It puts the numbers in as
Text so you don't lose any leading zeros. If you want them stored as
numbers, then remove the "'" & from the code above.

--
Regards,
Tom Ogilvy


"Foss" wrote in message
...
Mornin' all,

I've got a table with company details on it, then a list
of product codes that the company sells.
I want to write a new table, for the moment just a new
worksheet would be great, based on the product codes.

I'd like to take a record like this (where the ' - ' bit
specifies a new column):

R1FQ33 - 20780P,41930P
R7D41L - 52100S,84500S,42230P,45210P

and turn it into this:

R1FQ33 - 20780 - P
R1FQ33 - 41930 - P
R7D41L - 52100 - S
R7D41L - 84500 - S
R7D41L - 42230 - P
R7D41L - 45210 - P

There can be any number of items in the second column but they're always
separated by a comma. Each item in the cell can be four or five numbers
followed by a letter.

I can't get my head round how I'm going to do this. Let alone make it
work,
can anyone offer any sample code or pointers?

Thanks very much,
Foss