How to Separate a Full Name Into First and Last Names in Excel

Often, you'll receive a list of first and last names combined into a full name and need to separate them. Here's a quick method to easily accomplish this.

Contents:

1. Formula

2. Explanation

2.1. Extracting the First Name

2.2. Extracting the Last Name

2.3. Combining Both Formulas Together

Formula

To extract a first name, the following formula can be used:

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

To extract the last name, this formula can be used:

= RIGHT(A1 , LEN(A1) - SEARCH(" ", A1))

These two formulas will work as standalone formulas, but they can also be combined using HSTACK.

= LET(
  range, B3:B8,
  first_name, LEFT( range, (FIND(" ", range, 1) - 1)),
  last_name, RIGHT(range, LEN(range) - SEARCH(" ",range)),
  HSTACK(first_name,last_name))

You only have to change the range "B3:B8" in this example, to match your range of full names, and the formula will do the rest.

Explanation

There are two parts to this problem. How do we extract the first name, and how do we extract the last name.

Because first and last names are usually split up with a space separating them, using the space to separate out first name from right name is typically the easiest method.

How to Extract the First Name a Cell Using a Formula

To pull out the first name, we're going to search a cell for a space and return everything to the left of that space.

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

In this formula, the FIND function is looking for the position of the " " within the text in cell A1, and returning the position of " " in the text, let's say it's 5.

The LEFT function is then used to extract the text from the left side of that specific character. In this case, the formula is using the text in cell A1 and the number of characters from the left, which is the value returned by the FIND function - 1 (since we do not want to include the character itself).

How to Extract the Last Name from a Cell Using a Formula

Similarly, to pull out the last name, we're searching that same cell and returning everything to the right of the space.

= RIGHT(A1, LEN(A1) - SEARCH(" ", A1))

In this formula, the SEARCH function returns the position of the " " character in the cell, and the LEN function returns the total number of characters in the cell.

The RIGHT function then extracts all the characters to the right of the " " character by subtracting the position of the " " character from the total number of characters in the cell.

How to Combine Both Formulas Together

To combine both formulas together, we will be using the HSTACK function. This function allows us to horizontally stack two different array formulas.

The LET function at the beginning is only used to create a cleaned up formula, as this function allows you to names to calculations. For instance, "range" only needs to be defined as "B3:B8" once and can be used multiple times throughout the formula.

LET also allows us to call the entire first-name-extraction formula "first_name", and the last name formula as "last_name". Using those in the HSTACK function instead of the full, more complicated, formulas.

= LET(
    range, B3:B8,
    first_name, LEFT( range, (FIND(" ", range, 1) - 1)),
    last_name, RIGHT(range, LEN(range) - SEARCH(" ",range)),
    HSTACK(first_name,last_name))