Start a new topic

Isolating first or last name in a combined column

There may be times when you need to separate a column that contains a constituent's entire name, either as "John Doe" or "Doe, John."  You may already be familiar with Excel's text to columns function, which parses the name into two columns.  However, having to do this before every import is a pain.

 

Here's a way around it, within a profile:

In your Field Map, map the combined name field to Constituent Field > First Name.  Apply the First Name Only dictionary below, to isolate just the first name.

Then, create a virtual column in your Virtual Fields that copies the First Name column.  Apply the Last Name Only dictionary, to isolate just the last name.  

 

You can use this technique on name fields within any type of record, such as Constituent, Individual Relationship, etc.  As always, test the dictionary against a few examples in your data to make sure you are happy with the results before you apply it.

 

Here are two examples of single-cell name formats you may see, followed by the Excel format of the dictionaries needed to isolate each part of the name.  When you copy and paste from Excel to your dictionary, make sure to include the blank cells in the first column, as those are used to "remove" the unwanted part of the name.  

Example 1: Doe, John 

Keep only First Name:

image


Keep only Last Name:

image

 

Example 2: John Doe

Keep only First Name:

image


*Note that only the first name will be captured.  For example, "Mary Ann Smith" will result in "Mary," not "Mary Ann."

Keep only Last Name:

image

 

Login or Signup to post a comment