Insert a blank row at the top of your data, and put these formulae in
the cells stated:

B2: =IF($A1="",$A2,"")
C2: =IF($A1="",$A3,"")
D2: =IF($A1="",$A4,"")

(Note the similarities with these formulae)

E2: =IF($A1="",IF(OR($A5="",D2=""),"",$A5),"")
F2: =IF($A1="",IF(OR($A6="",E2=""),"",$A6),"")
G2: =IF($A1="",IF(OR($A7="",F2=""),"",$A7),"")

Then you can just copy these down to get the name and address in
columns B to G on the top row of your current addresses. You can fix
the values and then column A can be deleted.

The blank rows can be deleted by applying a filter to one of the
columns, selecting Blanks from the pull-down, highlighting the visible
(blank) rows and then clicking on Edit | Delete Rows. Then you can
select All from the filter pull-down to see your data in the format
you require.

Hope this helps.

Pete

On Dec 10, 9:35*am, Pai wrote:
I have around 1600 Name along with company
name,Address1,Address2,City,State,Pincode

For Ex:

Mr.hardeep Kanwar
Bi/200
Yamuna Vihar
Dehi
110053

Some times it has 5data as mentioned in the Example, Sometime4 and sometimes 6

And in the end of every data there is a Blank Row

Like This
Mr.hardeep Kanwar
Bi/200
Yamuna Vihar
Dehi
110053
Blank
Ms. Danielle Fran
American Cancer Society
1555 Abbey Road
East Lansing, MI 48823
Blank

Now i want to Convert like this

Mr.hardeep Kanwar * * * Bi/200 *Yamuna Vihar * *Dehi * *110053
Ms. Danielle Fran * * * American Cancer Society 1555 Abbey Road East Lansing, MI
48823

But Without using Marco.

Any Help will be Highly Appreciate

Hardeep Kanwar