Ever 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.
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.
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”.
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.
Your 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.
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.
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.
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?
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.