How to format a customer list in Excel

Published by: Erica Sanford    |   Category: "How To" Articles, Blog, Email List    

Understanding How to Move Customer Data from your CRM to your ESP

With such a large variety of business software options on the market to choose from, sometimes these platforms don’t interact as well as most of us would like them to. There are numerous Customer Relationship Managers (CRM’s) and Email Service Providers (ESP’s) on the market to choose from. But what happens if your CRM and your ESP clash – Don’t play nice together? Your ESP may tell you that your list was not formatted correctly or it needs to be in a different column order to be able to upload it into their platform. Or, when you get to the personalization stage, you don’t know why it is showing your customer’s full name instead of just their first name.

Below I’m going to explain how to reorganize or move data within an Excel Spreadsheet.


In the example above, you can see that I have the customer’s full name in column B. To split the column so that you have one column for first name and one column for last name, the next step is to go to the click the header of column C so that the whole column is selected. Then go to the CELLS section in the HOME tab and click the INSERT button.

Sorting Customer List

This will insert a new column and shift the original column C to column D.

Customer Email List Manipulation

The next step is to select Column B just like you did with Column C earlier and click the column header to highlight the whole tab. You will then go to the DATA tab and under DATA TOOLS and click TEXT TO COLUMNS.

Insert Email in List Excel

This will open up a “Wizard” box as shown below. The Wizard will detect what type of data you have. Click Next to proceed.

Email Import Wizard Excel

In this example, a single space separates the first and last names. So in the next screen of the Wizard below, only check “Space” in the DELIMITERS section. The Wizard will show you an example of how your new columns will look.  Then click NEXT.

Text to Columns Excel

The Wizard will ask you what type of data you’ll be formatting. In this example, it is Text. Choose GENERAL or TEXT in the COLUMN DATA FORMAT section by highlighting the new columns in the DATA PREVIEW and then click FINISH.

Clean Email List Import Excel

A pop-up will appear asking you if you want to replace the contents of the destination cells. Since we already cleared a new column for the data to go into in column C, and there is currently no data in those cells, click OK.

Excel Replace Cell Data

And Voila! Your data is now split into First and Last Name in two separate columns.

Moving Customer Data Excel

You can also perform the same steps to split a column that is separated by a comma such as my column D in this example. When you get to the step in the wizard that asks you which DELIMITERS you want to sort by, just ensure that you have selected the check-box for comma.



Sorry, comments are closed for this post.

Comments are closed.