As Francis wrote a few weeks ago, it’s important to import clean data into your Salesforce system. Gaining & keeping users’ trust is especially critical at the beginning of a project. When we work on a QuickStart project, the client is always tasked with preparing their data in a spreadsheet file with all the columns named and set-up. Even if you’re paying a consultant to manage your data import, you can save money by making sure you’re giving them the best data possible for import. And consultants won’t be able to catch anomalies in your data the way someone who’s more familiar with it can.
Whether you’re migrating from another database, or finally moving away from multiple spreadsheets to manage your data, there are a few principles you want to keep in mind, and some tools you can use in Excel or another spreadsheet program to achieve them.
Separating couples’ names
Many organizations have data where both members of the couple are listed in one field. If your data has a separate Last Name column, and you have a single column in which couples’ names are together, you’ll want to separate them (unlike Trish’s post from last week about sticking them together). You’ll have a lot more flexibility going forward with Salesforce if we use the 1 Contact = 1 Person concept consistently.
What you can do for data import, that will make your data preparation much easier, is to just separate the column with the two first names together into two columns:
- If there is a mix of “Chris & Alex” and “Robin and Terry” constructions: select the column that has the First Names. Do a search and replace for ” and “, changing it to ” & “. Make sure to include the spaces, so that we don’t end up converting “Andrew” to “&rew”.
- Insert 2 new columns to the right of the first names.
- In the first column, put the following formula (replace A2 with the cell reference for the combined first name):
=IF(ISNUMBER(FIND(“&”,A2)),LEFT(A2,(FIND(“&”,A2)-1))),A2) - In the second column, use this formula (again with the correct cell reference):
=IF(ISNUMBER(FIND(“&”,A2)),RIGHT(A2,(LEN(TRIM(A2))-FIND(“&”,A2))-1),””)
The first formula extracts the first First Names into its own column, or copies the stand-alone First Name if there’s no ampersand. The second formula extracts the 2nd First Name, or leaves the cell blank if there’s no ampersand.
