Clever Logic to strip a Address into its columns

[asolanki]

Hey Guys,
I have a puzzle that i cant seem to figure out if it can be done or not - to me it cant!! - due to the fact the source data is not consitent
and was wondering if any of you had come across a similar task and could help me figure it out if it even possible.

I have written a macro that extracts Addresses from a list

The addresses seem to be all over the place and never consitent
Eg

Holmes Court, 105 Gloucester Place, Marylebone, London, NW1
York Terrace West, london, NW1
Nottingham Terrace, London, NW1
Lisson Street, Marylebone, NW1
Brook House, 22 Cleveland Street, Fitzrovia, W1T
Ladbroke Grove, Notting Hill, W11
Kensington Gardens Square, Bayswater, W2
ELGIN AVENUE, MAIDA VALE, LONDON
Sevington Street, Maida Vale, W9
Portland Road, W11
Clarendon Road, London
Clifford Court, Bayswater, W2
Bell Street, Marylebone
Craven Terrace, Lancaster Gate, London, W2 3El
Flat 7, 64 Clarendon Road, Ladbroke Grove, London

Ideally the correct format is - Building name, Street Name, Area, 'London', Post code
As you can see from the examples - this format is not always adhered to and has data missing

I need to be able to split this address into the correct Columns( A, B, C,D) - Building name, Street Name, Area, Post code
the "london" part is not required and is to be ignored
But as the Addresses are all over the place in most cases - is there any clever logic out there that would help me do this(as a newbie to this type of logic - i just cant work out how i would do it)

Thanks in advance, for any guidance here - or stating the fact it cant be done which is my original assumption
Ash

 

[NdNoviceHlp]

I don't think clever logic will help. Garbage in, garbage out seems relevant. The input data must have some type of orderly structure to retrieve meaningful output. VBA is not magic. However, U may be able to use the Split function with "," seperator and then compare the function output to a dictionary object.... good luck. Dave

 

[asolanki]

Hey Dave

Totally Agree with you GIGO - Thats why i knew its not an easy one as the data is a mess.
I was thinking of going down the route of splitting it into the necessary parts using the "," and then running it through a "KeyWord macro" looking for words such as - Street - Road - Square - Place and so on to try and get some sort of order in the columns
But thanks for confirming its a difficult one.
I just wanted to throw it out there to see if anyone had mastered any logic to get round this
Thanks Again

Ash

 

[Kyle123]

I reckon you'd get some mileage in running it through a geocoding service. The data isn't easy to parse from scratch, but you should be able to leverage someone else's work. There are free and paid services out there, I generally find you get what you pay for, but OSM is good in the UK so you may find some good alternatives to google