Sorting/Moving

Hi all

I have a whole heap of addresses such as the following.

Lagoon Garage
20 Bears Lagoon RD VIC 3517
ph: (03) 5437 9248*Motor Engineers & Repairers

Landson Auto
20 Landson RD VIC 3157
ph: (03) 5437 9248 Motor Engineers & Repairers

All the words are in one Cell in COL A and there is a line between each address.

How do i sort of move the address and phone no so that they are in COL B and COL C so that i end up with a list.

I do not want to do this manually

Any help would be appreciated

Thanks
Andrew

Not sure whether there is a typo: "in one CELL"?? in this line <g>:
> All the words are in one Cell in COL A

ok, assuming the sample data below is really in one cell, say, A1:
> Lagoon Garage
> 20 Bears Lagoon RD VIC 3517
> ph: (03) 5437 9248*Motor Engineers & Repairers
>
> Landson Auto
> 20 Landson RD VIC 3157
> ph: (03) 5437 9248 Motor Engineers & Repairers

Select all the text in the formula bar for A1 > Copy

In a new sheet,
Right-click on A1 > paste
This should place each line of text in it's own row
The sample data will paste into A1:A7,
3 lines per set, with a blank line in-between

Now put in B1:
=OFFSET($A$1,ROW(A1)*4-4+COLUMN(A1)-1,)
Copy B1 across to D1, fill down until zeros appear,
signalling exhaustion of data extracted from col A

The above will re-lay the source data in col A neatly
into cols B to D, as required.

If necessary, freeze the values in cols B to C with an "in-place":
Copy > Paste special > Check "Values" > OK
then delete col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"koba" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all
>
> I have a whole heap of addresses such as the following.
>
> Lagoon Garage
> 20 Bears Lagoon RD VIC 3517
> ph: (03) 5437 9248*Motor Engineers & Repairers
>
> Landson Auto
> 20 Landson RD VIC 3157
> ph: (03) 5437 9248 Motor Engineers & Repairers
>
> All the words are in one Cell in COL A and there is a line between each
> address.
>
> How do i sort of move the address and phone no so that they are in COL
> B and COL C so that i end up with a list.
>
> I do not want to do this manually
>
> Any help would be appreciated
>
> Thanks
> Andrew
>
>
> --
> koba
> ------------------------------------------------------------------------
> koba's Profile:
http://www.excelforum.com/member.php...o&userid=28639
> View this thread: http://www.excelforum.com/showthread...hreadid=515151
>

Genius absolute genius

Glad it worked for you <g> !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"koba" <[email protected]> wrote in message
news:[email protected]...
>
> Genius absolute genius