UK Address Validation

[cornelce]

I have a list of UK addresses. Some are full, some are missing the postcode, some are missing the city name, but all are valid -- if you copy and paste the address into Google, they can all be found and are correct.

The current formatting only has spaces. No commas.

I am looking for a quick way to import all 25,000 lines and export in the correct (new) format.

Please take a look at the attached screenshot as that will make a lot of sense.

High quality screenshot: screenshot-5.jpg

Any and all help would be appreciated, thank you.

 

[cornelce]

Excel add ins and/or peripheral software recommendations accepted too. Thank you in advance.

 

[Kyle123]

The honest answer, if you want something quick and easy is to use one of the many online services that offer this. Most are paid for but are relatively cheap - whilst this may look like a simple problem, it is very very difficult - it's why services that off this are typically chargeable

I've used Loqate before, but their pricing can be a bit opaque, but you could try something like: Address Validation Free tool | Geoapify

 

[cornelce]

Unfortunately their formatting is not all that accurate, the output is a bit off.

I wondered if my spreadsheet could be section off. So the postcode separated. Then removed from column A.

Then all individual words separated into columns, but somehow have the output going from right-to-left. This is the bit I struggle with.

Capitalising only the first letter of each word (not for the postcode column).

Then merging again, with commas, in the correct format?

 

[Kyle123]

No, it's not possible without a 3rd party database like the ones I mentioned above. Your best best would be to use one of the services above to separate the address parts and then put them back together to match your requirements.

Getting the postcode is the easy bit. However, taking an example from your spreadsheet, a computer wouldn't know whether the below:

FLAT 15 THE CHILTERNS GROVE HILL SOUTH WOODFORD LONDON

Separates as any of the below:

FLAT 15 THE CHILTERNS GROVE, HILL SOUTH, WOODFORD LONDON
FLAT 15 THE CHILTERNS, GROVE HILL, SOUTH WOODFORD LONDON
FLAT 15 THE CHILTERNS GROVE HILL, SOUTH WOODFORD, LONDON

You need to be able to break the address apart and then compare against a broken apart properly formatted PAF file, to know how to rebuild it.

 

[cornelce]

Got it, thank you!