The formula and method I posted will not completely do what you want.

Follow the steps I gave you and you will wind up with three columns

name-street........city-state.........zip

As JP pointed out, you will then have to manipulate the first two columns using
DataText to Columns or a couple more helper columns and more formulas to break
up into two more columns to achieve your 5 columns.

Formulas for splitting text strings can be found here.

http://tinyurl.com/2w9dta

What part of the steps I gave are you having trouble with?

You can copy the formula directly from my post into B1.

To drag/copy across you select B1 then hover your cursor over the bottom right
corner of the cell.

You will see a black cross and a small black lump. Left-click and drag across.

Same for copying down.


Gord

On Sat, 29 Mar 2008 18:11:00 -0700, help please
wrote:

Gord, I do not fully understand your answer, I am not that great with excel,
would you explain your answer for a dummy please, and thank you very much.

"Gord Dibben" wrote:

In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:A))

Drag/copy across to D1.

Select B1:D1 and drag/copy down until zeros show up.

Select columns B:D and copy.

EditPaste Special(in place)ValuesOKEsc

Delete Column A


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 20:38:00 -0700, help please
wrote:

how do I change the lay out of an excel spread sheet, I have name address
city state and zip in column A all the way down, in lines of 3 about 900
addressess. I want to change them to the top of the page across, with
name-street-city-state-zip. I cant seem to get it to do what I want, any help
would be greatful.