In the third of a series of guest blogs, Alan Sawyers of A.S. Design, Web & Marketing looks at his top five tips for tidying up customer email lists in Excel.​..

I look after several ecommerce sites. Some of them my own, some of them for clients.

And one of the things I get asked regularly is “can we export a list of customers from our website?”. The answer is usually “yes” but exports never look how you want them to.

Here are my top tips for creating a cleaned-up customer list, so you can use it for email marketing.


TIP 1: Delete what you don’t need

If you’ve exported a customer list from a system (for example WooCommerce), it will usually have lots of columns you don’t need for your email marketing. In most cases, all you will need is a first name, last name and email address.

I would recommend you keep your download and start a new spreadsheet for your email list. For this example, we are going to start a new sheet with 2 columns: name and email address, which looks like this:


TIP 2: Remove duplicates

Now you have a spreadsheet with a list of names and email addresses, you might want to check that your list doesn’t have the same customer more than once.

To do this…

  1. Select all the data in your new sheet
  2. Click on the Data tab
  3. Click Remove Duplicates
  4. Tick the box next to both columns
  5. Click OK

You will see that our original list includes 11 names and email addresses, but Adam Birch and Jill Darlow are repeated. Once we run the Remove Duplicates function, this should happen:


TIP 3: Clean up BLOCK CAPITALS and lower case names

The first thing we’re going to do is standardise the format of names, so that each first name and last name has a capital letter at the start, but nowhere else.

This means, when you’re using personalisation to email customers, you will end up with “Dear Brian” and not “Dear BRIAN”, which doesn’t look very professional, even if it was the customer who entered it that way.

FORMULA #1: =PROPER(A2)

  1. Insert a new column after column A, so column B will become blank
  2. Copy FORMULA #1 into cell B2
  3. Copy or duplicate the formula down column B until all names are tidied

Our list now looks like this:

We don’t need two columns of names, so we can delete column A. But before we do, we need to remove the formulas:

  1. Give column B the heading Name
  2. Highlight all of the data in column B under the heading
  3. Copy all of the data
  4. Then right click and select Paste Values

You can now delete column A and your new tidied list of names will become column A.


TIP 4: Split first name and last name in Excel

Now you have a list of names in column A, you want to split the first and last names so that you can personalise your emails with “Dear Brian” and not “Dear Brian Jones”.

To begin, insert two new columns after the Name column, making columns B and C blank.

Firstly, let’s get the first name…

FORMULA #2: =LEFT(A2,SEARCH(” “,A2)-1)

  1. Give column B the heading First Name
  2. Copy FORMULA #2 into the top cell of column B under the heading
  3. Copy or duplicate the formula down column B until all first names are found

Now, let’s get the last name…

FORMULA #3: =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

  1. Give column C the heading Last Name
  2. Copy FORMULA #2 into the top cell of column C under the heading
  3. Copy or duplicate the formula down column B until all first names are found

Our list now looks like this:

Next, if you don’t want to have a column with full names, and just the new columns with first name and last name, we want to convert those formulas into permanent text.

  1. Highlight all of the data in columns B and C under the header
  2. Copy all of the data
  3. Then right click and select the Paste Values icon (or Paste Special, Paste Values)

You can now delete column A.

ADDITIONAL TIP FROM LARA:

Alternatively, these steps can be replaced by the following:

To begin, insert a new column after the Name column.

  1. Select all the data in column A
  2. From the data tab select Text to columns
  3. In step 1 of the wizard keep the selection on delimited
  4. In step 2 tick the Space box (the Tab box can remain ticked or not)
  5. Leave the settings as they are for step 3
  6. Give Column A the heading First Name and column B the heading Last Name

The full name in column A will now be replaced with the first name in column A and the surname in column B.


 

TIP 5: Remove hyperlinks

If you have email addresses in a data list, you probably don’t need them to be hyperlinks, and it can become annoying when you want to click on a cell and instead it opens up an email.

So, my final tip is this:

  1. Select all of the data in column C
  2. Right click and select Remove Hyperlinks

Our final list now looks like this:


About Alan Sawyers

Alan helps businesses sell online by working with them to create user experiences that are right for them. He has worked with Lara since 2018 and helped completely rebuild this website so that it better reflected Lara’s personality and training style.

Visit A.S. Design, Web & Marketing here for more information.

Want to learn more about the software you have installed? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Courses I run. 

MEMBERSHIPS

FOLLOW LARA