Another play to try ..

Assuming data is in col A, from row1 down, with no blank rows between the
"blocks" of 3 lines

Put in B1:
=OFFSET($A$1,ROWS($A$1:A1)*3-3+COLUMNS($A$1:A1)-1,)

Copy B1 across to D1, fill down until zeros appear, signalling exhaustion of
data from col A
(Since you have 500 blocks, fill down till D500 thereabouts)

Cols B to D will return the data from col A in the desired format

Freeze the values in cols B to D with a copypaste specialvalues either
in-place or elsewhere
---
And if there's 1 blank row in-between each "block"

Put instead in B1:
=OFFSET($A$1,ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1)-1,)

(Rest of the steps remain unchanged)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Upendra Singhai" wrote in
message ...
I have once sheet with more than 500 blocks of data in this format:


--------------------------------------|-----------------|--------------
comapny1
--------------------------------------|-----------------|--------------
Website: web1
--------------------------------------|-----------------|--------------
Location: loca1
--------------------------------------|-----------------|--------------



How can I convert them to a arranged format like this:
--------------------------------------|-----------------|--------------
COMPANY | WEBSITE | LOCATION
--------------------------------------|-----------------|--------------
company 1 |web1 | loca1
--------------------------------------|-----------------|--------------
company 2 |web2 | loca2
--------------------------------------|-----------------|--------------
company 3 |web3 | loca3
--------------------------------------|-----------------|--------------

Upendra Singhai
______________
visit: www.upendrasinghai.com