Excel and Google Sheets are common tools used for storing business data such as leads for your business.

There might come a time when you need to separate a list of full names into first and last names for importing into a email marketing platform such as MailChimp, Constant Contact, Brevo, or any other system that requires separated name columns.

Below are two formulas you can use to separate Full Names column into single first and last names.

1. The first step is to insert two columns after the Full Name column. Change the A1 to the appropriate column as needed if the Full Name column is not in column A
2. Copy the first formula into Row 1, column B.
3. Copy the second formula into Row 1, column C.
4. Copy and paste the formulas down the two columns or drag the columns by the bottom right corner of the cell so the formulas are in the entire columns for B and C.

That’s it. Save your file.

Use this formula to extract the first name from the full name:

=LEFT(A1, FIND(" ", A1) - 1)

Use this formula to extract the last from the full name:

To pull the last name from a full name in Excel or Google Sheets, you can use the combination of several functions such as FIND, RIGHT, and LEN. Assuming the full name is in cell A1, you can use the following formula in another cell to extract the last name.

=TRIM(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Let’s break down the formula to understand how it works:

1. SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) replaces the last space in the full name with an “@” symbol. This helps to identify the last name by finding the position of “@” later in the formula.
2. LEN(A1)-LEN(SUBSTITUTE(A1," ","")) calculates the number of spaces in the full name.
3. FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) finds the position of “@” symbol, which represents the last space in the full name.
4. LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) calculates the number of characters from the last space to the end of the full name.
5. RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) extracts the substring from the last space to the end of the full name.
6. TRIM(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) trims any leading or trailing spaces from the extracted substring, giving you the last name.

Splitting a full name into separate columns in Excel or Sheets can save you time and effort when working with large datasets. Whether you’re managing a customer database or analyzing survey results, this simple process can make your workflow much smoother. By following the steps outlined in this post, you’ll be able to split names into first and last columns quickly and accurately.

Give it a try and see how much easier your data analysis becomes! Good luck with your data management endeavors.