address help (similar to trim or transpose)

[Aaaaa]

I need to convert data in an excel file from:

Brimelows Shop 2 15 Howey Pl MELBOURNE VIC 3000 (03) 9671 3733
Bungendore Leather & Ginninderra Leather Ginninderra Vlge Gold Creek
NICHOLLS ACT 2913 (02) 6230 2770

to:
Brimelows // Shop 2 15 Howey Pl // MELBOURNE // VIC // 3000 // (03) 9671
3733
Bungendore Leather & Ginninderra Leather // Ginninderra Vlge Gold Creek //
NICHOLLS // ACT // 2913 // (02) 6230 2770
where "//" are seperate columns.

ie convert A1 (Brimelows Shop 2 15 Howey Pl MELBOURNE VIC 3000 (03) 9671
3733) to
B1 Brimelows
C1 Shop 2 15
D1 Howey Pl
E1 MELBOURNE
F1 VIC
G1 3000
H1 (03) 9671 3733

The positioning of the data varies, A1 is different to B1 as per above just
to make it more difficult.

Can someone help please.

Pls reply to NG or email to petersmith37AThtmailDOTTcomm

pls replace server name to hotmail.com

Thanks in advance
Pete

 

[Guest]

I suggest you look at using a combination of:

1) Search and replace with wildcards to get your text in a more regular form.

2) Data->Text to Columns

Regards: Colin