Separating First Names and Last Names from Full Names
Creating formulas to separate people’s full names into their first names and last names using functions including FIND, SEARCH, LEFT, RIGHT, LEN, TRIM, REPT and SUBSTITUTE.
Last updated on 2018-09-18 by David Wallis.
Many of my clients have spreadsheets in which a person’s name is entered into a single cell. What’s need is a way to separate the first part of the name into one cell and the last part into another cell. To achieve this, what’s also need are formulas to achieve the results that can be copied when new names are added to the list.
We’ll start by considering well composed lists of names and 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 in 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:
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.
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:
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:
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.
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:
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:
Give DMW’s Tips Your Support
ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.
Thanks, in anticipation.