Basic list prep in Excel – splitting a full name into component parts
Originally posted in 2013. Note: while this specific exercise could be done using Microsoft Copilot or ChatGPT, the core concepts and approach are foundational to many other data transformations. The key concept is the mindset...not the specific activity of writing a formula.
While it's easier to import data to Dynamics CRM using the data templates, prepping that data can take a little work. One of the most common things to deal with is splitting a "Full Name" from a source file, which needs to be imported to CRM as "First Name", "Middle Name", "Last Name".
Let's assume the source file uses the format of "[Last Name], [First Name] [Middle Initial]". This makes it just a little too complex to use the Text-to-Columns feature in Excel (but note, if you're dealing with a basic "Last, First" format, keep it simple and use Text-to Columns).
Easy enough to do by hand...but what a hassle if you're doing a thousand records or more.
Our solution? Build 3 simple formulas to handle that manual work.
Looks like gibberish, right? Read on for an explanation
Like Billy Blaze, I find it's easiest if we break things down.
Start with the Last Name (Column C)
We know that the last name starts at the left of the cell:
=LEFT(A2,FIND(",",A2)-1)
English: Grab some characters from the left-hand side of A2. How many? One character behind the comma, so go find the comma in A2, then go back one character.
Pluck out the First Name (Column D)
This part is a little trickier, but not too much. We can't use the left() function any more since it's in the middle.
=MID(A2,LEN(C2)+3,LEN(A2)-LEN(C2)-4)
English: Grab some characters from the middle of A2, starting 3 characters after the last name. How many should we grab? The total length of the Full Name, minus the length of the Last Name, minus four characters (2 spaces, 1 comma, and 1 Middle Initial)
Grab the Middle Initial (Column E)
This one's the easiest to decipher...
=RIGHT(A2,1)
English: Grab some characters from the right-hand side of A2. Just 1 this time.
With those three formulas, here's what we end up with:
Looks much prettier now.
The rabbit hole goes deeper
When you're sent a file to work with, it's pretty rare that names fall into a perfect little "Last, First MI" format. There's a few ways to deal with this. I've put together a workbook with this and a few other examples of splitting up full name data, which you can feel free to thieve from me.
- First MI Last
- Last, First [optional MI]
- First [optional MI] Last
These cover a few different methods for carving up the data, but are by no means exhaustive.
Note, it's important to know your data that you're starting with. Take some time to ask some questions: Are all of the names in the same format? How should a record be handled if the name is missing? Is there funky CaPITAlizAtion that needs to be dealt with? Does anyone have two middle initials, or maybe a space in the middle of their name? Picking through these issues is often the most time-consuming part of prepping a list of data.
I'm always tempted to try to write some "super formula" that is so smart it can handle any case you run across...but remember, if there are only twenty records where there are two middle initials or a space in their first name, it's often quicker to do those twenty by hand and use a simpler formula to help out with the rest. You've been warned
Once you've split the name into component parts, you can copy/paste these columns into a CRM data template and import the results!