Extract Website URL from Email Address using Excel for Microsoft CRM Salesforce ImportEver received a trade show list after an event that has email address but doesn’t have website, with a request to import it into your CRM solution?  You know those website URLs are locked up inside of the email addresses, but you need to get them imported in a hurry, so you just import it without concern for the website.  The problem is that the website can contain critical information.  It can be used by tools like InsideView and Data.com to identify the company and pull in other information.  And it can be used by sales when doing quick research prior to making a call.

This article explains how to use an Excel formula to quickly pull the website URL out of an email address before importing into your CRM solution.

1. Get the CRM Import List Into Excel Format.

Since most lists are provided in XLS or CSV format, this is usually as simple as opening the file.  But, if your list is already in your CRM solution, you may need to export it (in a re-importable format); or if it is in a non-Excel format, then you can usually use Excel tools to convert it.

2. Add a Formula Column to Convert Email Address to Website URL

Create a new column at the end of your Excel import file.  Using the formula below, change “Q2” to whatever column has the email address in it.  To briefly explain the formula, it says "start with the standard website prefix, then find all the email address text to the right of the @ sign and add it to the string”.

="http://www."&MID(Q2,SEARCH("@",Q2,1)+1,LEN(Q2))

3. Copy the Formula to All Rows

Now copy formula to all rows of the input file so that you have the website URL, derived from the email address, for every row you plan to import.

4. Copy the Formula Column to a Value Column

Excel Paste CRM Email to Website URL as ValueYour CRM application (such as Salesforce or Microsoft Dynamics CRM) probably isn’t going to want to import a column with a formula in it.  So you need to convert it to a value.  Highlight the entire column and press CTRL+C (for “copy”).  Now position the cursor at the top of the target column and right click then select the “Paste Special” option from the pop-up menu.  Select the “Values” radio button and then press OK.  The values (but not the formula) will be pasted into the target cells.

Note that you can choose to paste the cells over the formula cells if you wish.

5. Bonus: Clean Up Free Email Domains

No matter where you get your list from, there are always some pesky leads that provide non-corporate emails (such as gmail.com or outlook.com).  Ideally, you don’t want to import “http://www.gmail.com” as a website URL. Better to leave it blank in those cases.  But there are A LOT of different free email services out there.  Wading through each individual record can be very time consuming.

For moderate-sized list imports, the best way to clear these up is to just sort the entire list by website URL, then scan through them and delete the clusters that are really just free email addresses.  If you’re working with a very large lists, and you do so on a regular basis, then you can either create a CRM workflow or a much more sophisticated Excel formula to drop those. We’ve had to create those approaches for our own lists and for clients who outsource their list import processes to us.

6. Now Just Stir, Drink … and Import!

Make sure you save it (you may need to save in CSV format, depending on what formats your CRM tool allows you to import), then go into your CRM solution and import the list.

Next Step: Nurturing and Qualification

 

When you’re done, follow your standard nurturing and lead qualification processes to be sure that none of those leads that your organization spent thousands of dollars to acquire slip through the cracks.

… you DO have a lead qualification and nurturing process … right?