How to separate names in Excel

Excel users often want to separate a column of names into different columns for each part of the name.

For example, to turn:

Into:

Separating a name into separate parts allows you to:

• Use just the first name in personalized communications.
• Sort by last name.
• Filter by honorific.
• Insert the data into datasets that expect the name in thsi format.

It is possible to split a name column at spaces in Excel using the Data>Text To Columns function. But this only works well if there is exactly one of each part of the name for every name. Which is rarely the case.

However it is very easy to split name columns in Easy Data Transform in a few clicks, using the intelligent Split Name transform. Even if the data is quite messy.

The splitting algorithm knows about:

• Common honorifics in a range of languages, such as: ‘Mr’, ‘Mrs’, ‘Miss’, ‘Ms’, ‘Herr’ and ‘Frau’ and their abbreviations.
• Military, political, religious and other honorifics, such as: ‘Major General’ and ‘Rabbi’.
• ‘Particules’ at the start of last names, such as: ‘de’ and ‘von’.
• Common suffixes, such as: ‘MBA’, ‘PhD’ and ‘Jr’.

You can also add your own custom honorifics and suffixes.

See the video above for a quick example of splitting an Excel name column.

For more information on the Split Name transform, see the Split Name documentation.

Easy Data Transform also has transforms for removing duplicates, cleaning up whitespace, changing capitalization and much more. This allows you to combine multiple transforms to quickly and easily create complex data transformations, processing millions of rows at lightning speed.