Here's a sample file:
http://flypicture.com?display=updone&id=qtz3lqk=
Sheet1 contains the earlier suggestion
Sheet2 contains a slight variation (with all blank rows removed)
as explained below

Another way ..

Provided the number of lines for each data set (minus blank rows) is exactly
the same, then this slight revision may be worth a try ..

Removing all blank rows in the col A:
Select col A (the original data)
Press F5 Special Check "Blanks" OK
Right-click on the selection Delete Shift cells up OK

Now, with the blank rows removed,
each data set will comprise say, exactly 20 lines ..

Insert 2 new cols to the left of the data
In the new cols A & B:

Put in A1:
=OFFSET(INDIRECT("B"&INT((ROWS($A$1:A1)-1)/20)*20+1),,)

(same formula as previous, except adjusted for 20 lines per data set,
instead of 22 lines)

Put in B1:
=IF(ISNUMBER(SEARCH("Cost Center:",C1)),MID(C1,SEARCH(": ",C1)+2,4)+0,"")

(no change, same formula as previous)

Select A1:B1, fill down until the last row of data
Col A should return the labelling that you're after
Kill the formulas in both cols A & B with an "in-place" copy paste special
values ok, then delete col B

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"ecohen1" wrote in
message ...

It's working for the first cost center, but after I just have some
blanks.
Please could you send me an excel file with a sample to


Thanks for your help


--
ecohen1
------------------------------------------------------------------------
ecohen1's Profile:
http://www.excelforum.com/member.php...o&userid=12988
View this thread: http://www.excelforum.com/showthread...hreadid=388070