One way which might do it ..

Assuming data in cols A and B, from row1 down

In C1:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Surname";"First Name";"Home
Number"},A1)))0,TRIM(MID(A1,SEARCH(":",A1)+1,99)) ,"")

In D1:
=IF(AND(C1<"",C2<""),C2&" "&C1,"")

In E1:
=IF(ISNUMBER(SEARCH("Email",B1)),TRIM(MID(B1,SEARC H(":",B1)+1,99)),"")

In F1:
=IF(D1="","",ROW())

In G1:
=INDEX(D:D,SMALL(F:F,ROW()))

In H1:
=IF(ISNUMBER(C1+0),ROW(),"")

In I1:
=INDEX(C:C,SMALL(H:H,ROW()))

In J1:
=IF(E1="","",ROW())

In K1:
=INDEX(E:E,SMALL(J:J,ROW()))

Finally, in L1:
=IF(G1="","",G1&"#"&I1&"#"&K1)

Then just select C1:L1, copy down to the last row of source data in col A.
Col L should return the required results bunched at the top (till #NUM!
appears). Tested ok on your sample data as posted.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lamb Chop" wrote in message
...
I have a list of the contact list exported from a software which I have no
control of what it generate.

For example,

Column A Column B
Surname: Smith Title: Prof
First Name: John Email:
Address: Virtual Street
State: Victoria
Postcode: 1299
Home Number: 123456
Fax: 456789
Mobile: 98765
<empty row.
Surname: Peters Occupation: Teacher
First Name: Davis Email:
Address: Virtual Street
Home Number: 99999
Mobile: 98765
<Empty row
<another record starts
...
...
..
etc...


I need to put the name, Home nuimber and emails into a single column and
separate the fields by #, e.g.

John
Davis
...
..
...


The trick of the data is that the lenght of the records are not the same,
e.g. some missed the Fax number, some missed the address. Therefore,
some
records occupy 5 rows and some with 6 or 7 rows. Some use 2 columns,
while
some use only 1 column. (never more than two columns).
Between each record there is a empty row to separate them.

All the field started with some key words, e.g. "Surname:", "First
Name:",
"email:" etc

Any suggestion?

Thanks in advance.