Excel Tips when preparing data for CRM upload

By Enabliser Natasha 

Anyone using a CRM has at some point been looking at uploading data (be in general updates, lists, or new data fields) – and we all know how much easier the data migration is if the data is prepared to match the fields in CRM. Great in theory right; but often when you get these lists they are in Excel and not quite matching the fields you need to map to. So here are a few of the most common data manipulation issues I come across that need addressing in Excel. Historically Excel has not been my tool of choice but it seems it can do more than I have traditionally given it credit for.

First problem: I have a single name column and I need to split this into two columns to identify the first name and the surname.

Here is my data in Excel

To ensure we do not overwrite any data add at least one column to the right of the Name column. Note if you have three names in the single column then add at least two new columns to the right.

Go to the Data Tab and click the Text to Column option to launch the Wizard:

Select the relevant data – but only select one column.

This will launch the below Wizard:

Check Delimited the checked and the default, click next.

The next screen will appear; ensure the Delimiter is Space – you will see the column preview appear below and the names are now separated

Click Next again and the final page will appear:v

Click finish and the final confirmation will appear as per below:

Column has now been split into two columns:

Second problem: The text case is wrong, for example I want mixed case not just upper case.
Here is my data in Excel – in the below example all my company names are in upper case I want mixed case.

Add an extra column and call it whatever you like, I’ve called it Company2

Add the relevant calculation / equation for the case change you wish – in this example I want Proper text so I start typing my calculation
My other options are:
= Upper(B1) – converts text to all upper case

= Lower(B1) – converts text to all lower case

= Proper(B1) – converts text to proper case, or title case (the first letter of each word is capitalised)

And complete the sum and reference the column

Done; just copy the equation for all relevant fields.

For more information contact us

Add new comment