How much of this data cleaning job can be sped up / automated by Excel?

[DDLSDW]

Hello,

My company is currently working on cleaning a database of roughly 22'000 customers. We have exported the customer list to Excel and want to make sure all the data is formatted correctly and in the right fields before re-importing into our system.

The formatting is not an issue, a few =PROPER and =TRIM formulas and some Find & Replacing will do the trick.

The problem is moving information into the correct columns and making everything structured and uniform.

The process for inputting and importing data has changed over the years and the result is that the data is very random. Certain standards were only adopted later on (eg. using US as a country code, whereas before it could be anything from U.S., U.S, USA, U.S.A, United States, America, etc...)

Additionally customers might have inputted their addresses in different ways, eg. city and state separated by a comma or space in one field instead of having them in different fields. It has even gone so far as in customers inputting their numbers next to their names (eg. Mike 0123456789).

I would post a screenshot however to protect our customers' privacy I am unable to do so.

Currently the only way we'd know how to clean this is to manually go through each entry. We've been advised to hire an 'Excel wizard' who can do all sorts of things with Excel through formulas, automation, macros, etc... We have however been asking ourselves how helpful this might be considering the high level of randomness and lack of structure with the data.

Would you recommend hiring an expert freelancer for the job or giving the data to an admin / data center to clean manually? With the latter there is obviously the risk of handing the data to scammers who will sell the information or phish the customers. This is a bit of a tough situation and any advice is much appreciated.

Many thanks in advance,
David

[davesexcel]

When supplying a sample workbook to the forum, you don't actually supply the workbook, you supply a mock-up of the workbook. That's why when we ask for a sample workbook we make sure to explain not to include real data.

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

To add a file to a new post

To add a file to an existing post.

[DDLSDW]

Many thanks, here is the sample workbook. I hope that something like this is what you were looking for?+