# How to Use Excel to Separate First and Last Name from Full Name

How to create formulas in Microsoft® Excel to separate peoples’ full names into their first names and last names using functions including FIND, SEARCH, LEFT, RIGHT, LEN, TRIM, REPT and SUBSTITUTE.

Last updated on 2020-05-26 by David Wallis.

## Preface

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

We’ll start by considering well-composed lists of names and then move on to cover others that are not straightforward. As we do so we’ll need to create formulas that comprise of more that one of Excel’s functions. This will provide the opportunity to explore techniques for constructing formulas with functions embedded one within another.

## Separating Out First Names

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

Our strategy 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 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 are 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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

*Thanks, in anticipation*.