address help please

[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 {each line in a single cell}

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. Cells H1, G1 & F1 have fixed widths. Cell E1 is
in uppercase.

Can someone help please.

Pls reply to NG or email to petersmith37AThtmailDOTTcomm

pls replace server name to hotmail.com

Thanks in advance
Pete

 

[Guest]

Hi

If there are no strict rules for dividing the information, it cannot be
done. You could try Data/Text to columns, but unless there is a system, it
won't be perfect.

Andy.

 

[Ron Rosenfeld]

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 {each line in a single cell}

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. Cells H1, G1 & F1 have fixed widths. Cell E1 is
in uppercase.

Can someone help please.

Pls reply to NG or email to petersmith37AThtmailDOTTcomm

pls replace server name to hotmail.com

Thanks in advance
Pete

I believe your data may be too ambiguous to parse correctly.

For example, there does not seem to be any method to reliably separate your
first two columns -- they can be different widths and have different content.

Some of the cells can be parsed out using "Regular Expressions"

First download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/


I'll have to make some assumptions:

1. The last capitalized word of three characters should go in F1, and all
valid strings have a three character capitalized word.

F1: =REGEX.MID(A1,"(?<=[A-Z]\s)[A-Z]{3}\b")

2. Capitalized words preceding the last capitalized word go in E1:

E1: =REGEX.MID(A1,"([A-Z]{2,}\s)+(?=\b[A-Z]{3}\b)")

3. An isolated, 4 digit string that follows the three digit capitalized word
will go in G1.

G1 =REGEX.MID(A1,"(?<=\b[A-Z]{3}\s)\d{4}\b")

4. Since you said H1 was fixed length, I assume it is also fixed format:

H1: =REGEX.MID(A1,"\(\d\d\)(\s\d{4}){2}\b")


I don't see any unambiguous method to parse out columns B, C or D.
--ron