Useful Data Cleanup Functions in Excel

November 12, 2009

We are often hired to import existing data into Microsoft Dynamics CRM, and much of this data comes to us in the form of Excel spreadsheets.  Most of these spreadsheets require some form of data cleanup before they can be imported into CRM.

We thought it might be helpful to post a list of functions we commonly use for cleanup in Excel.  Knowing these Excel features can make your life a *lot* easier if you are tasked with preparing data.

Concatenate: Use this functions to bring two fields together.  Let’s say, for example, you have purchased data from a mail house.  The

Proper: Sometimes, we get Excel spreadsheets where all company names are in UPPERCASE.  Use the Proper function to change this data to just capitalize the first letter of each word.  With company names like ABC Company, you’ll have some extra cleanup, but this function should clean up most of the company names.

Clean: Especially with data originally exported from Outlook, it’s common to see non-printing characters (like a hard return) in Excel cells.  You can use the Clean function to remove these non-printing characters en masse.

Data Transpose: Rows represent records, and columns represent fields.  If you have a spreadsheet that is set up the opposite way (with each column representing a record) then you can use the transpose function to switch the data around easily.  Copy all data, go to a new Excel spreadsheet, and Click Edit | Paste Special.  Then  choose the Transpose option and click OK.

Search/Replace: It may seem obvious, but the search/replace feature in Excel is a lot easier if done before you import data into CRM.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: