Splitting full names into first and last

TL;DR:

• Text to Columns splits once and leaves static results. Formulas keep updating when the names change.

• FIND the space, then LEFT and MID pull out the first and last parts. Sheets has SPLIT, and recent Excel has TEXTSPLIT.

• Middle names and multi-part surnames break simple rules, so decide a rule and check the odd rows.

The sign-up export has a single Full name column, yet the mail merge wants First name and Last name separately. Hundreds of rows are too many to retype. You can automate the split safely, provided you review the odd names afterward.

When splitting works, and when it does not

Splitting is easy when every name follows one pattern: first name, a space, last name. It becomes a judgment call when it does not. Middle names, two-word surnames such as de la Cruz, titles like Dr, and names that are written surname first all break the simple rule.

There is no formula that splits every human name correctly. The practical approach is to split by a clear rule, then scan the results for names that do not fit.

Before you start

• Work on a copy of the column, or keep the original Full name column beside the new ones.

• Remove extra spaces first with =TRIM(A2), since a double space between words would break the position of the split.

• Insert two empty columns to the right, so the results do not overwrite data.

• Sort or filter by name length to spot unusual rows later.

Splitting with Text to Columns

Select the Full name column, then use Data and choose Text to Columns in Excel, or Split text to columns in Google Sheets (the exact wording varies by version). Choose Delimited if asked, and pick Space as the separator.

The names are cut at each space and spread over as many columns as there are words. This is quick, but the results are static: if you change a name in the original, the pieces do not change. Also, it writes over whatever is in the cells to the right, so make sure they are empty.

Splitting with LEFT, MID and FIND

Formulas keep working when the list changes. The idea is to find where the space sits, then take everything left of it or right of it.

1. Find the space: FIND(" ", A2) returns the position of the first space.

2. Take the first name: =LEFT(A2, FIND(" ", A2) - 1) returns the characters before that position.

3. Take the last name: =MID(A2, FIND(" ", A2) + 1, 100) returns everything after the space. The 100 just means long enough.

4. Fill down: Copy both formulas down the column and spot check some rows.

Worked example: splitting "Ana Lopez" into two columns

In Ana Lopez, the characters are A, n, a, a space, L, o, p, e, z. The space is the 4th character, so FIND(" ", A2) returns 4.

• First name: LEFT(A2, 4 - 1) takes the first 3 characters, which is Ana.

• Last name: MID(A2, 4 + 1, 100) starts at character 5 and returns Lopez.

Check by counting. Ana has 3 letters, Lopez has 5, and with the space that is 3 + 1 + 5 = 9 characters, matching the 9 characters listed above. If a name has no space at all, FIND returns an error, so wrap the formulas in IFERROR when your list might have single-word entries.

Splitting with SPLIT in Sheets and TEXTSPLIT in Excel

Google Sheets has a single function that does the same job in one cell: =SPLIT(A2, " "). It fills the cell and the cells to its right with each word.

Recent versions of Excel (Microsoft 365) have TEXTSPLIT, used as =TEXTSPLIT(A2, " "). Older Excel versions do not have it, so if the file will be opened elsewhere, the LEFT and MID formulas are safer. Both functions spill into neighbouring cells, so those cells need to be empty.

Middle names and other awkward names

Now Ana Maria Lopez. Cutting at the first space yields First name Ana and Last name Maria Lopez, which is wrong when Maria is a middle name. So decide on a rule up front. A common one: the first word is the first name and the last word is the last name.

To get the last word with a formula, use this classic trick:

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

It replaces every space with 100 spaces, takes the rightmost 100 characters, and trims away the padding, which leaves only the final word. For Ana Maria Lopez it returns Lopez. Combined with LEFT(A2, FIND(" ", A2) - 1) for the first name, you get Ana and Lopez, and the middle name is simply ignored.

This rule has a cost. Maria de la Cruz would give a last name of Cruz, losing de la. Surnames with several words and names where the surname comes first cannot be split reliably by position, so list them for review and fix them by hand.

Names are not simple: Be careful about assuming everyone has a first and a last name that fit this pattern. If the names belong to real people, keep the original full name column so nothing is lost.

Checking the result

• Sort the first-name column and read the top and bottom: stray titles, blank cells and initials collect there.

• Filter for rows where the original has more than one space. Count spaces with =LEN(A2)-LEN(SUBSTITUTE(A2, " ", "")). For Ana Lopez that is 9 − 8 = 1.

• Check that the first and last names joined with a space equal the original again: =B2&" "&C2=A2 should be TRUE.

Doing it faster

For a one-off list of a few dozen names, Text to Columns is the fastest. For a list that keeps growing, use formulas. If you are not sure where the command is in your app, press DuckyHelper's key and ask out loud, and it can see your screen while you carry on with the sheet. Whichever way you choose, the spot check at the end is not optional.

Frequently asked questions

Can I undo Text to Columns?

Yes, immediately with undo. Later, you can only recover the original if you kept a copy of the Full name column, so keep one.

Why do I get #VALUE! with the formula?

FIND failed because the cell has no space. Wrap the formula in IFERROR or fix that name.