I have a list of addresses that I should like to convert to a table, they
each contain 4 fields, as follows:

John Doe
123 Street
Anywhere
Anytown
Bill Bloggs
456 Road
Somewhere
Someplace

I should like to convert these names to:

John Doe 123 Street Anywhere Anytown
Bill Bloggs 456 Road Somewhere Someplace

The list contains several hundred names and addresses so idealy I would like
an easier way to do this that repetitive cut and paste.

Paul

Assuming the list is in column A.......

In B1 enter =INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($A:B)-1)

Copy across to E1 then copy B1:E1 down until you get 0's


Gord Dibben MS Excel MVP


On Tue, 1 Aug 2006 14:36:01 -0700, Paul <(E-Mail Removed)> wrote:

>I have a list of addresses that I should like to convert to a table, they
>each contain 4 fields, as follows:
>
>John Doe
>123 Street
>Anywhere
>Anytown
>Bill Bloggs
>456 Road
>Somewhere
>Someplace
>
>I should like to convert these names to:
>
>John Doe 123 Street Anywhere Anytown
>Bill Bloggs 456 Road Somewhere Someplace
>
>The list contains several hundred names and addresses so idealy I would like
>an easier way to do this that repetitive cut and paste.
>
>

Paul
Does each address cover 4 cells? If not, is there something somewhere
to distinguish the end of one address and the start of another? HTH Otto
"Paul" <(E-Mail Removed)> wrote in message
news:6B9078F3-6345-44BC-B31E-(E-Mail Removed)...
>I have a list of addresses that I should like to convert to a table, they
> each contain 4 fields, as follows:
>
> John Doe
> 123 Street
> Anywhere
> Anytown
> Bill Bloggs
> 456 Road
> Somewhere
> Someplace
>
> I should like to convert these names to:
>
> John Doe 123 Street Anywhere Anytown
> Bill Bloggs 456 Road Somewhere Someplace
>
> The list contains several hundred names and addresses so idealy I would
> like
> an easier way to do this that repetitive cut and paste.
>
>
>

Thanks Gord - works perfectly

"Gord Dibben" wrote:

> Paul
>
> Assuming the list is in column A.......
>
> In B1 enter =INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($A:B)-1)
>
> Copy across to E1 then copy B1:E1 down until you get 0's
>
>
> Gord Dibben MS Excel MVP
>
>
> On Tue, 1 Aug 2006 14:36:01 -0700, Paul <(E-Mail Removed)> wrote:
>
> >I have a list of addresses that I should like to convert to a table, they
> >each contain 4 fields, as follows:
> >
> >John Doe
> >123 Street
> >Anywhere
> >Anytown
> >Bill Bloggs
> >456 Road
> >Somewhere
> >Someplace
> >
> >I should like to convert these names to:
> >
> >John Doe 123 Street Anywhere Anytown
> >Bill Bloggs 456 Road Somewhere Someplace
> >
> >The list contains several hundred names and addresses so idealy I would like
> >an easier way to do this that repetitive cut and paste.
> >
> >
>
>

Hi Otto,

Yes all addresses cover 4 cells. Gord's reply has given me a solution.

Thank you for answering,

Paul

"Otto Moehrbach" wrote:

> Paul
> Does each address cover 4 cells? If not, is there something somewhere
> to distinguish the end of one address and the start of another? HTH Otto
> "Paul" <(E-Mail Removed)> wrote in message
> news:6B9078F3-6345-44BC-B31E-(E-Mail Removed)...
> >I have a list of addresses that I should like to convert to a table, they
> > each contain 4 fields, as follows:
> >
> > John Doe
> > 123 Street
> > Anywhere
> > Anytown
> > Bill Bloggs
> > 456 Road
> > Somewhere
> > Someplace
> >
> > I should like to convert these names to:
> >
> > John Doe 123 Street Anywhere Anytown
> > Bill Bloggs 456 Road Somewhere Someplace
> >
> > The list contains several hundred names and addresses so idealy I would
> > like
> > an easier way to do this that repetitive cut and paste.
> >
> >
> >
>
>
>