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

[JohnTopley]

You will (almost certainly!) need VBA to clean your data but getting a perfect set of addresses is unlikely

In your sample file please add the required results.

Many of [Address Line 2] (to me) would appear mandatory and determining whether an entry in this column (for example) is a "City" will be difficult.

[bongomark]

Thanks for the reply. I've updated the sample spreadsheet with required results. - I've actually tweaked the sample data as well as I realised I hadn't given an example with the postcode without spaces.

Yea, understand that perfect set of addresses may not be possible, but as close to that as possible would save a lot of time.

Thanks,
Mark

[sandy666]

is that what you want?
(tailored to your example)

[bongomark]

Yes, sandy666 that looks great thanks. How do I run that on other sheets?

[sandy666]

How do I run that on other sheets?

more details please

[bongomark]

So I get sent lots of spreadsheets of addresses and need to run the process that you've done each time I receive one. Is it possible to have a sheet setup that I can drop the data into each time, which then formats it correctly in a new sheet?

[sandy666]

sorry but no
solution was tailored to your example with individual approach
you can do that if you create your own M (Power Query) to each new data
in your example there are no rules and it's hard to create a universal solution

[JohnTopley]

Please Login or Register  to view this content.

Ouput in blue highlighted area

Copy data into A:F (as per this file) and click RUN: output will be column G onwards.

I am sure there will be other "anomalies".

[bongomark]

That's amazing, thanks John.

You're right about anomalies - I've just found a couple!....
1) If address line 1 is empty but address line 2 is populated is it possible to move the data to address line 1?
2) If a postcode is missing we just get an error. If it possible for it to report which cell or line the postcode is missing from?
3) A colleague has just given me two other examples of how we sometimes get data supplied. Is it possible to incorporate these on the same sheet, or would they need a separate one?

I've attached another sheet with examples of each of the above.

Thanks again so much,
Mark

[sandy666]

could you post excel file with one hundred rows of data not only a few?
both kinds (will be 200 rows)

[bongomark]

Hey Sandy666, attached two new files now. Again would need to be able to clean up new spreadsheets on a regular basis, not just the attached ones.

[sandy666]

Thanks, I'll look at it a bit later and try to find some algorithm

[sandy666]

i think it would be useful to list the cities and towns of all your country or those that were, are or will be used in these files
I understand that the district should not be treated like a city

[sandy666]

where do you get such a mishmash?

check this one, based on postcodes

[JohnTopley]

a "quick" sample extract: Added CITIES (thanks to Sandy) but needs further work!

Please Login or Register  to view this content.

[sandy666]

@John
download file from post#15 and unhide tab, there will be list of 2864 cities/towns and counties

[JohnTopley]

@Sandy: thank you - updated my post (#16) to include city search.

[sandy666]

@John
My pleasure

[sandy666]

you quit from this thread 3 days ago so ...

---

if the problem is solved, make us happy and hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

[bongomark]

you quit from this thread 3 days ago so ...

---

if the problem is solved, make us happy and hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

Hi Sandy666,

Apologies, I've been away for a few days. Thanks for helping and sorting that data out, that is really helpful. However I need to be able to process new data regularly. Is there a way to have this in a form like the sheet JohnTopley did so that I can drop new data in each time?

Many

[sandy666]

did you try paste new data into the source table then refresh green table? (on sheet one)
I can't test it because I have no new data

due to the variety of unexpected entries (column D), operator (your) intervention will sometimes be required

but if John's solution works you don't need others

[bongomark]

That's amazing, thanks John.

You're right about anomalies - I've just found a couple!....
1) If address line 1 is empty but address line 2 is populated is it possible to move the data to address line 1?
2) If a postcode is missing we just get an error. If it possible for it to report which cell or line the postcode is missing from?
3) A colleague has just given me two other examples of how we sometimes get data supplied. Is it possible to incorporate these on the same sheet, or would they need a separate one?

I've attached another sheet with examples of each of the above.

Thanks again so much,
Mark

Hi JohnTopley,

Is it by any chance possible to amend the sheet you produced with points 1) and 2) added to it?

Thanks,
Mark

[JohnTopley]

The attached identifies Missing Post Codes (row 97) BUT the data you supplied is not in thhe same format as your original so there is no "Address Line 1" or "Address line 2" differentiation.

And addresses with no delimiter (",") are almost impossible to split (as per attached) into components (Address Line 1/Address Line 2) other than Post Code and Cities (thanks to Sandy's list).

[sandy666]

[removed by s666]

[bongomark]

The attached identifies Missing Post Codes (row 97) BUT the data you supplied is not in thhe same format as your original so there is no "Address Line 1" or "Address line 2" differentiation.

And addresses with no delimiter (",") are almost impossible to split (as per attached) into components (Address Line 1/Address Line 2) other than Post Code and Cities (thanks to Sandy's list).

Hi John,

I think if we could base it on the initial spreadsheet that I sent over that you, as that is the most common way in which I receive the addresses, that would be great. Would it be possible to add the two points below to that, or will it not work?
1) If address line 1 is empty but address line 2 is populated is it possible to move the data to address line 1?
2) If a postcode is missing we just get an error. If it possible for it to report which cell or line the postcode is missing from?

When I get the data in a different format, Sandy666's sheet will help I'm sure, and we'll manage those as best we can.

Thanks for all your help,
Mark

[JohnTopley]

Please Login or Register  to view this content.

No complete solution for addresses without delimiters.