How To Separate Names In Excel (3 Easy Ways)

Have you ever looked at a list of names on a computer, where all the names are mixed up and hard to understand? 

You want to email these people, but you can’t just address them as “JohnMary.” It’s important to separate their first and last names so your emails look professional and are sent to the right people.

But don’t worry! This guide will show you three easy ways to tidy up your messy list. You’ll become a pro at organizing names with just a few clicks or some smart formulas. 

Then you can use your clean list for targeted email marketing or any other task that needs organized information.

Press the button to get a PDF guide on splitting names in Excel. You can learn at your own speed and have the info handy whenever needed.

#1 Method – How To Separate Names In Excel 

1. Make a new space in your list of names by clicking next to where the names are listed. Then, choose “Insert” from the menu that appears.

2. Go to the “Data” tab.
3. Click on the top of the list where all the names are.
4. Select “Text to Columns.”

5. Choose “Delimited” and click “Next.”

6. Unselect “Tab” and choose “Space,” then click “Next.”

7. Click “Finish.”

8. Rename the columns so it’s clear which one is for first names and which one is for last names.

If you have extra columns you don’t need, you can delete them or put them together before you add your list to Constant Contact.

• For names like “Mary Margaret” without a hyphen, you need to put both parts in the “First Name” column by yourself.
• Middle names like “Mary Margaret Jones” aren’t recognized as a standard part of a contact’s name. You can add them to the “First Name” column or keep them separate and add them as a custom detail when you’re putting in your list.
• If you had couples like “Mary and John Jones” listed as one contact, you need to separate them into two rows now. Each person should have their own email address. You can delete the column that says “And.” If you only have one email address for both, you can put both first names together in the “First Name” column.

Read More: How To Remove Duplicates In Excel Using 3 Different Methods?

#2 Method – How To Split Full Name To First, Last, And Middle Name

To split names in Excel, you might need different methods depending on how the names are written.

For names like “First name, Middle name, Last name,” you can use these formulas:

A	B	C	D
1	Full name	First name	Last name
2	FirstName MiddleName LastName	=LEFT(A2,SEARCH(” “, A2)-1)	=MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1)
Result	David Mark White	David	Mark

1. For the first name, you can use the LEFT SEARCH formula.
2. For the last name, you find the position of the second space, subtract that from the total length of the name, and then use the RIGHT function to get the characters from that position till the end.
3. To get the middle name, you need to know where both spaces are. Find the position of the first space, add 1 to start from the next character, and use this position in the MID function as the start point. Then, subtract the position of the first space from the position of the second space, subtract 1 to remove an extra space, and use this number in the MID function to specify how many characters to extract.

For names like “Last name, First name, Middle name,” you can use similar formulas.

A	B	C	D
1	Full name	First name	Last name
2	LastName, FirstName MiddleName	=MID(A2, SEARCH(” “,A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2) + 1) – SEARCH(” “, A2) -1)	=RIGHT(A2, LEN(A2) – SEARCH(” “, A2, SEARCH(” “, A2, 1)+1))
Result	White, David Mark	David	Mark

And if there are suffixes like Jr. or III, you can also use a similar method.

A	B	C	D
1	Full name	First name	Last name
2	FirstName LastName, Suffix	=LEFT(A2, SEARCH(” “,A2)-1)	=MID(A2, SEARCH(” “,A2) + 1, SEARCH(“,”,A2) – SEARCH(” “,A2)-1)
Result	Robert Furlan, Jr.	Robert	Furlan

That’s how you can split names in Excel using different combinations of functions. If you want to understand the formulas better, you can download our sample workbook.

Tip: If you’re using Excel 365, you can use the TEXTSPLIT function to split names by any separator you choose.

Read More: Top 10 Excel Formatting Tips To Make Your Life Easier

#3 Method – How To Separate First And Last Name From Name With Comma

To split a name in the format “Last name, First name” into two separate columns, you can use these formulas:

Formula to extract first name

For the first name:

=RIGHT(cell, LEN(cell) – SEARCH(” “, cell))

This formula finds the space between the last and first names, then extracts the characters to the right of it, giving you the first name.

Formula to extract last name

For the last name:

=LEFT(cell, SEARCH(” “, cell) – 2)

This formula locates the space between the last and first names, then takes the characters to the left of it, minus two (to exclude the comma and the space), giving you the last name.

So, if the full name is in cell A2, the formulas look like this:

For the first name:

=RIGHT(A2, LEN(A2) – SEARCH(” “, A2))

For the last name:

=LEFT(A2, SEARCH(” “, A2) – 2)

Here’s what the results would look like:

This guide has shared three easy ways to fix the problem of how to separate names in excel. With these tricks, you can turn your messy list into an organized one.

Keep in mind, having a neat contact list is crucial for effective email marketing, personalized messages, or any job that needs correct info. 

FAQs