Try this formula pasted into the column A in the cell adjacent to the
first line of your data (assumed to be 5 in the example)

=IF(OR(ISERR(IF(FIND(",",B5)0,1,0)),ISERR(IF(FIND ("TOTAL",B5)0,1,0))=FALSE),A4,B5)

and then copy it down for all the remainder of the rows.

You can then convert this to values by selecting column A, then copy,
then paste special - values.


If works by looking for commas in the cells in column B and finding
those which have a comma but not the word TOTAL. For these, it returns
the value of the cell in column B. For the others it returns the value
of the cell above. It follows, therefore, that this approach only works
if there is an unbroken column of formulae.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=564903