Sorting/Moving

[koba]

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

 

[Max]

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

---

 

[koba]

Genius absolute genius

 

[Max]

Glad it worked for you <g> !