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


--
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php...o&userid=28639
View this thread: http://www.excelforum.com/showthread...hreadid=515151

Sorting/Moving
 

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" wrote in message
...

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

Sorting/Moving
 

Genius absolute genius


--
koba
------------------------------------------------------------------------
koba's Profile: http://www.excelforum.com/member.php...o&userid=28639
View this thread: http://www.excelforum.com/showthread...hreadid=515151

Sorting/Moving
 

Glad it worked for you <g !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"koba" wrote in message
...

Genius absolute genius