Address Cleaning.....

[bongomark]

Hi,

We get sent a lot of addresses by clients and despite our requests for how we need the data submitted there are always errors in it. - stray characters, empty fields, extra spaces, no spaces etc.

I'm looking for a formula (or formulas!) to help clean address in a list of addresses. I've attached some sample data of a sheet showing the typical errors we might get. Can anyone help? - What's the best way to then clean the data each time we get sent a spreadsheet of addresses?

Summary of how we need the data in order to be able to upload it (for couriers etc)

1) Recipient name must be 1 column, and can?t be split into two columns for first name and surname.
2) Recipient name, Address Line 1, City and Postcode must always be populated.
3) Address Line 2 is an optional field and it doesn?t matter if this is empty. Referencing the address below I have shown as an example? if someone had written ?4 Allan Close, Enfield? into Address Line 1 column, we would separate it out so it moves ?Enfield? into Address Line 2 field. Also, if someone had written ?Enfield, London? into the City field and Address Line 2 was empty, we would move ?Enfield? back into the Address Line 2 field.
4) There can't be any commas or special characters
5) Postcodes must have a break (one space) in the letters and can?t be one unbroken line. For example if someone wrote EC1V4RX we would create it to be EC1V 4RX

Thanks in advance,
Mark