Steve Robertson
posted by Steve Robertson on May 29, 2013
Find me on: Steve Robertson on LinkedIn

CRM Data Integrity: How To Import Clean Data

Checking in for a flight at the airportWe often talk about CRM Maturity and how customer-focused information is handled within your business. At the very basic foundation level, you need accurate, relevant, on-time and accessible information on customers/clients and prospects so that people can get on with the task at hand without having to waste time on checking and correcting inaccurate or even false data.

With this in mind, what happens when you need to import data from an external source, and the information is not formatted in a uniform, consistent way? How do you ensure good, clean data enters your CRM system? Here, we take a look at a few tips and best practice examples.

The task in hand

You have received an Excel file to import that looks like this:


You spot the following issues:

  1. The Name column containing 4 elements of data need splitting out into separate columns
  2. The Company column has data that has been incorrectly entered and should be “Capitalise Each Word”
  3. The Phone column has phone numbers with a missing leading zero

Remember: The best time to clean the data is before the import happens.

So first things first: save a copy of the Excel Worksheet and make all your changes in that. Periodic saving is also useful in case you make a mistake… you can then revert back to your previous copy. And always check the data – the formulae and splitting of data will help in most cases but not all.

The result you want:

How to Split Data from One Column into Multiple Columns

1. Insert blank columns to the right of the cells you want to convert into multiple columns. You can find the number of columns you need to add with the following:

  • Array formula (CTRL + SHIFT + ENTER):

=MAX(IF(LEN(TRIM(A2:A5))=0,0,LEN(TRIM(A2:A5))-LEN(SUBSTITUTE(A2:A5,” “,””))))

Remember to use CTRL + SHIFT + ENTER as it is an array formula.

2. Rename the original column to the data it is going to hold e.g. Name -> Prefix

3. Select the cells you want to convert.


4. Click the “Text to Columns” button in the “Data Tools” group on the “Data” tab.

importing-data-55. Select the Original Data type that best suits your existing data.


If you’re separating text that is variable in length, such as a first name and last name, select Delimited. If all cells contain a specific number of characters, choose Fixed Width.

6. Click Next. The option you see next depends on which data type you selected in the previous step.

  • If you selected Delimited in Step 4, select (or type) the character you use to separate your text. You can also check that you have added the correct number of columns.


  • If you selected Fixed Width, click the ruler bar where you want the data to split.

7. Click Next.

8. Assign a data type to each column, if necessary.

Select a column at the bottom of the dialog box and then select a data type (General, Text, or Date) for each column. For numbers other than dates, choose General; click the Advanced button to add a thousand separator or to indicate how many decimal places to show.

9. Click Finish.

10. After you have checked again that you have added the correct number of columns click “OK”.

Excel splits the selected cells into multiple columns.

11. Now check and correct the data for the columns that you just split as the data may be in the incorrect columns.


12. Save your changes to the workbook.

How to Capitalise Each Word

1. Insert a column to the right of the Data you are going to correct.

2. Insert the following formula:


3. Copy the formula down the rows that you want to correct.


4. Check and correct the data in your new column for the exceptions to the rule, for example:

  • Bbc should be BBC
  • Abn Amro should be ABN AMRO

5. Now “Copy” the data from your column and “Paste Values” back into the original column.


6. Now you can delete the column you added.


7. Save your changes.

Correcting Phone Numbers

Phone numbers and Excel sometimes don’t play together very well. Excel is a tool for calculations so Excel attempts to help the user by removing leading zeros from numbers which isn’t particularly helpful for a telephone number.

There are a number of different ways to fix the issue – the main thing being the format of the phone number you require and the data that you have in Excel.

In the example data there was only one number that need fixing – 1959560410 needed to have a zero added to become 01959560410. The following formula also adds a + symbol if the first 2 characters of the phone number are 44. Please note this formula is just for UK numbers.


1. Copy the formula.

2. Paste the formula into the cell and adjust the cell references as required.


3. Copy the formula down the rows that you want to correct.


4. Check and correct the data in your new column for the exceptions to the rule, for example: Non UK Phone numbers.

5. Now “Copy” the data from your column and “Paste Values” back into the original phone number column.


6. Delete the column with your phone number formula in.

7. Save your changes.

Remember – it is much easier tidying up the data before it gets imported into your CRM system, rather than correcting all the mistakes afterwards.

Download the Business Owners Guide

  • Leave a Reply