How to Separate First Name and Last Name from Full Name in Excel

Many of my clients had spreadsheets in which a person's full name was entered into a single cell. What was needed was a way to separate the first part of the name into one cell and the last part into another cell.

Last updated on 2024-05-09.

Separating Out First Names

We'll start with this conveniently well-composed list:

Our strategy for the first name will be to pick out all the letters of each full name leading up to the space between the first and last names. So, firstly we need to find out at how many letters along the full name the space is located.

To do this we can use Excel's FIND function, which has this structure:

FIND(find_text, within_text, start_num)

find_text is the character, or sequence of characters, for which you're searching.

within_text is the text within which you're searching.

start_num tells FIND at what point in within_text you want it to start searching. Inclusion of a value for start_num is arbitary. If you don't give start_num a value, FIND will start searching at the beginning of within_text.

The character we want FIND to locate — in this case the space — is enclosed in speach marks:

We now use Excel's LEFT function to isolate the letters to the left of the space. Since the space is one character beyond the end of the first name, we tell LEFT to take one less character than the value FIND alone gives us:

Now that we've understood how both FIND and LEFT can be put to work, we can combine them into a single formulae for the first name:

As you get familiar with new functions like FIND and LEFT, using columns as we've done here to explore them can act as a confidence booster.

Once familiar you can create the finished formula all in one go. I recommend practicing a few times to become fluent before showing off to colleagues watching over your shoulder.

Separating Out Last Names

A last name is all the letters (characters) to the right of the space. We'll use Excel's RIGHT function to do that. RIGHT has this structure:

RIGHT(text,num_chars))

text is the text to which you're applying your formula.

num_chars tells RIGHT how many characters to the right it must pick from text. num_chars is arbitary. If you don't give it a value RIGHT will pick one character.

For our formula, num_chars is equal to the total number of characaters in the full name minus the number of characters up to, and including, the space character.

FIND will locate the space, as we've already seen; Excel's LEN function will tell us the total number of characters in the full name. LEN's structure is simply LEN(text):

Now we combine LEN with FIND to calculate the number of letters in the last name:

We're ready to invoke RIGHT to return that number of letters:

Excel's TRIM Function

Extra spaces often crop up where they're not necessary or required. I've experienced them in many spreadsheets and had to remove them. They could have appeared in our list of names:

Excel's TRIM function removes all sequences of spaces in a string of text, replacing each one with a single space. TRIM is very easy to apply. In our formulas where we're referencing the cell containing the full name, simply wrap the reference in a TRIM:

Problems Separating Names

The names in the lists above are neat and tidy — a full name comprising of two names separated by a single space in all cases. Rarely do list contain names that make things that easy for us. This list includes some full names that do not fit the neat-and-tidy pattern:

Our existing formula for the first name continues to do the job required; but middle names and intials have foxed the last-name formula. So we'll concentrate on the last name. Here's my suggestion for a new formula:

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

REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))

This formula takes some getting used to. So, in the following sections I'll break it down.

Excel's REPT Function

REPT repeats a character, or sequence of characters, a given number of times. REPT has this structure:

REPT(text,num_chars)

text is the character, or sequence of characters, that you require to be repeated.

num_chars tells REPT how many times to repeat text.

In our full-name formula, REPT repeats a space a number of times that is equal to the LENgth of the full name (TRIMed of superpfluos spaces). I've used a fixed space font and dots for spaces for ease of counting characters:

“Oi You” comprises of six characters. So our REPT makes six spaces.

REPT supplies the third argument of the SUBSTITUTE function.

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

REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))

Excel's SUBSTITUTE Function

SUBSTITUTE repeats a character, or sequence of charaters, a given number of times. SUBSTITUTE has this structure:

SUBSTITUTE(text, old_text, new_text, instance_num)

text is the string of characters in which you want to change some of its characters.

old_text is the character, or sequence of characters, within text you want replaced.

new_text is the character, or sequence of charaters, to use instead of old_text.

instance_num is optional. When you give if a number, it tells SUBSTITUTE which occurence of old_text in text to replace with new_text. When you leave it blank, SUBSTITUTE replaces each and every occurence of old_text.

Here is SUBSTITUTE in action in the setting of our formula for the last name:

I've used a fixed space font and dots for spaces. What SUBSTITUTE does is replace each space in the full name with the number of spaces returned by REPT:

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

REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))

Completing the Last Name Formula

We've examined the actions of SUBSTITUTE and REPT. When included in our formula as they are, their combined effect is to make a string of characters that is significantly lengthier than the full name itself.

If we take the number of characters in a full name (minus the number of any leading and trailing spaces removed by TRIM) and count that number from the right-hand end of the Last Name column, we see that only the person's last name (and some leading spaces) are covered by the count:

So now's the point at which we apply the RIGHT function:

Leaving a final TRIM to clear away the leading spaces to complete our formula:

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

REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2))))

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.