How To Separate Names In Excel (3 Easy Ways)

Angelina Robinson

How To Separate Names In Excel

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.
25phWQiYhgS3208prJ9 KGaXI3x6ykjygXT8lHaxrtAUg dMrlP1kMwR ECD7qTMtKnn3I46EQEJVPi52 E8tiFXBwcLmw5YN4 80N8nVZlFcxRQAWxkyMY7bdjxnjDWR80dashr b8ECRHiYN4dVA4

  1. Go to the “Data” tab.
  2. Click on the top of the list where all the names are.
  3. Select “Text to Columns.”
vyUyUBEwu QbM1xVBGu3j8MUihGptqsiF8KRCsXK0f nJgOf9P3I6D6l1TbvjbBW2IIkcXqOMARRfESdSbR9jWAXBFsXw7WovvkkDxi3DX XJue2HBgX92wBjK39R4FoNgt4xIWWMgdEeFOTf H5h0

  1. Choose “Delimited” and click “Next.”
BSiWwSfTIHV9rP7Y xz7yQ4hOKLUAmrMm 6w6 sq9SxxmIdTtDKRmODvqVRxm5aG5WcPwohNfboIeFuhZDIVCWFQqT68hwm4ddKpzqodhsjCX8j18YXwrkZIeXaUU2H aO3iAcgz8krJShlDw 4we3E

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

  1. Click “Finish.”
xvQ rpSd5O47Gvvlbp8WWFGTmBpHkGZmdzWsIscHVOrC0RZohbOyOWYosbK7lkeoPMPkztUazoFulhAUDLv Pu62eGfREA3C 9Dmqkfa268rvwspvJ9Z4JQCGYmgJnHr60XkearPddDj0LnXvQjAvMg

  1. 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:

1Full nameFirst nameLast name
2FirstName MiddleName LastName=LEFT(A2,SEARCH(” “, A2)-1)=MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1)
ResultDavid Mark WhiteDavidMark
  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.
g9IoJOPZSNmME2t9ccNXDRSjs3lSeSU11TuM5628BZ40oDiv4GHoiSYDULSG6OFtd9OcSyHhXC wjpJmSegL7O5OXwn 5r4 TtVvz2mHTV82n986IQ4CY31zS7z01EvqEGr66mVH7rTe5UpcjS32WAI

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

1Full nameFirst nameLast name
2LastName, 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))
ResultWhite, David MarkDavidMark

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

1Full nameFirst nameLast name
2FirstName LastName, Suffix=LEFT(A2, SEARCH(” “,A2)-1)=MID(A2, SEARCH(” “,A2) + 1, SEARCH(“,”,A2) – SEARCH(” “,A2)-1)
ResultRobert Furlan, Jr.RobertFurlan

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:

bynLwTDgbv k1GOEmlvtsiJJWIC1EM8yBDnmckOOS71r4I1VTcUpJWFdWI6OGTVNu39IM4Bwj12M7u0QvPRT42Duk6c8ED

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. 


What Is The Formula To Separate Two Words In Excel?

In the new box, type =SPLIT(B1,”-“) or =TEXTSPLIT(B1,”-“) in Excel. Put B1 in the box where you want to split the cell, and put a dash (-) where you want the cell to break.

How Do I Separate Text In Sheets?

To split text in Google Sheets, you have two options: you can use the ‘SPLIT’ function or the ‘Text to Columns’ feature. With the ‘SPLIT’ function, you type =SPLIT(A1, “,”) into a cell to split the text in cell A1 at each comma. For the ‘Text to Columns’ feature, you select the cells containing the text you want to split, then go to the ‘Data’ menu. From there, choose ‘Split text to columns’ and pick your separator, like a space or comma.

What Is The Text To Columns Characteristic In Excel?

Text to Columns is a feature in Excel that helps you split data from one column into several columns using a specific symbol or character. It’s handy when you want to divide things like names, addresses, or any info that needs to be organized into different groups.

About the author

Hey, it's Angelina Robinson! If you're confused by Excel, don't worry, I've got your back. I've spent years mastering it, and I want to help you make the most of it.

I got into Excel because I was fascinated by everything it can do. Now, I help people and companies use it better for their work.

So, my blogging story started when I met my friend Angelina Robinson. We hit it off and decided to team up. Now, in our 50s, we've made to share what we know with the world. My thing? Making tricky topics simple and exciting.

Leave a Comment