Data cleaning for CRM imports
Names
Split a full name into first and last name for a CRM import
Most CRMs want separate First name and Last name fields, but your list has a single Name column, so it has to be split. The naive tools get real names wrong: Excel's Text to Columns mangles "Mary Jane van der Berg" and "Smith, John Jr.", and PROPER() turns McDonald into Mcdonald. In Sift you split the column on a delimiter, keep the casing right, and approve every split with a before/after diff before you export. It runs in your browser, so nothing is uploaded.
Split your name column in Sift, free →
The manual fix (and where it breaks)
In Excel you have two options, and both break on ordinary names.
Text to Columns splits on a delimiter. Point it at a space and "John Smith" becomes John | Smith, which is fine. But "Mary Jane van der Berg" explodes into five columns, and there is no clean way to say "everything after the first word is the surname". A last-name-first entry like "Smith, John Jr." splits into the wrong order entirely, with the suffix hanging off the end. The LEFT / RIGHT / FIND formula approach has the same problem: FIND(" ", A1) finds the first space, so anyone with a middle name or a two-word surname lands in the wrong field.
PROPER(), the usual fix for ALL-CAPS lists, capitalizes the first letter after every space and lowercases the rest. So MCDONALD becomes Mcdonald, MacLeOD becomes Macleod, and O'BRIEN survives only by the accident of the apostrophe. It has no concept of an internal capital, so every Scottish, Irish, and Dutch name it touches comes out wrong, and you fix them back by hand.
Split names in Sift
- Choose the delimiter and split. Pick the Name column, choose the delimiter (a space, or a comma) and how many target columns you want, and split it into First and Last. The last target column receives the remainder, so you decide where the leftover words go.
- Fix the casing. Sift's casing fix preserves McDonald, O'Brien, and van der, and keeps acronyms like RevOps intact, so an ALL-CAPS or lowercase list comes out correctly cased without PROPER's damage.
- Combine columns back if you need to. If your CRM wants a single Name field instead of First and Last, Sift can join the columns back into one.
- Review every split with a before/after diff. Every change is shown as a diff, so you can approve the clean splits and catch the irregular ones before you export.
What Sift can't fix
Be clear-eyed about this: Sift's split is a delimiter split, not an intelligent name parser. It cannot reliably know where a compound surname begins, so it will not automatically understand that "van der Berg" is one surname rather than three words, and it cannot reorder a "Smith, John" that is written last-name-first into "John" and "Smith" on its own. That is why Sift shows you every split to approve rather than guessing, and lists the rows that need a second look. The clean majority, "John Smith", "Jane Doe", split perfectly; the genuinely irregular names still need your eyes. That is the honest trade: a tool that is right where it can be, and honest where it can't, instead of one that guesses silently and leaves you to find the mistakes inside your CRM.