Formatting an address

[Matt27]

I have a spreadsheet with two columns of information. The first column contains an account number which is fine. The second column contains an address, which is unformatted. Some of the addresses start with a comma, so for example it may start ", 25 Harlow Avenue, ". Other addresses may start as I would want them to start, for example "Flat 42, ". Some of the addresses also have 2 or 3 commas in the middle of the address. Two examples of my data:
, 38, GLYNDEBOURNE GARDENS, , , BANBURY, OXON, OX161XW
CORNER HOUSE, , ST ANDREWS ROAD, , LITTLESTONE, NEW ROMNEY, KENT, TN288PY
Is there any simple way I can split the address out, so I have the house number and road in one cell (e.g. B1), the town in the next cell (e.g. C1), the county in the following cell (e.g. D1) and the postcode in the final cell (e.g. E1) AND remove the commas at the same time??

 

[SkipVought]

Hi,

You may be in luck. The COMMAS seem to be field delimiters.

I took the two rows of data that you posted and parsed it using Data > Text to columns, Delimiter - COMMA...

and got this...
[tt]
A B C D E F G H
38 GLYNDEBOURNE GARDENS BANBURY OXON OX161XW
CORNER HOUSE ST ANDREWS ROAD LITTLESTONE NEW ROMNEY KENT TN288PY
[/tt]
I don't know if this help, as I am not familiar with British addressing.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Bong]

Well, I think the split function is going to be your friend here but you're going to have to make some assumptions about what information is definitely there and where in the address it occurs. For example, if the cell, say "B1" contains: 42 Zubruzlechester, Middle Cronley, Vogshpere, then a = split(cells(1,2),",") will result in an array, a with 3 elements: "42 Zubruzlechester", "Middle Cronley", and "Vogshpere". In the case where the cell, say "B2" starts with a comma: ,42 Zubruzlechester, Middle Cronley,, you'll get 4 elements where a(0) = "" and a(3) = ""

_________________
Bob Rashkin