Leave cell B1 empty.

Enter this formula in B2 and copy down as needed:

=IF(LEFT(A2,4)="port","",MID(LOOKUP(10,SEARCH("por t",A$1:A1),A$1:A1),6,255))


--
Biff
Microsoft Excel MVP


"Rich Hayes" wrote in message
...
Hi,

I have a query on some data i'm currently analysing and it's causing me a
headache. I'm hoping there is a simple solution to this that doesn't
involve
macros.

Any help much appreciated

Sample data: this is what i have in one column of data at present. each
name
beneath a port represents people working at that particular port. However,
what i want is in the column along side this data for it to show the port
name that each employee works at.

what i have at present is as follows;

cell a1 port:rotterdam
cell a2 richard
cell a3 david
cell a4 paul
cell a5 port:south africa
cell a6 james
cell a7 sam
cell a8 keith
cell a9 duncan
cell a10 port:port talbot
cell a11 simon
cell a12 rachel

what i'd like to see is a formula in column B to return the relevant port
for each individual (shown below) It is a large document with over 5000
rows
so a formula is a must if possible.

column A column B
Port: rotterdam
Richard rotterdam
David rotterdam
Paul rotterdam
Port: south africa
james south africa
sam south africa
keith south africa
duncan south africa
Port: port talbot
simon port talbot
rachel port talbot