Tech Tip Tuesday –
Excel tricks: Converting one name field into two (first and last)
Using formulas to extract certain data from a field can be
extremely useful (and fun). One common example is when you have a list of names
with both the first and last name included, and you need to have the list
separated into two fields (first and last name). If the names are consistently
separated by one comma and one space, you can use this formula to extract the
first and last names separately into two fields.
In this exercise we’ll use three formulas. All three are
very useful, but become even more powerful when combined. They are:
LEFT(text,num_chars) – Returns the specified number of
characters from the start of a text string.
MID(text,start_num,num_chars) – Returns the characters from
the middle of a text string, given a starting position and length.
FIND(find_text,within_text,start_num) – Returns the starting
position of one text string within another text string. FIND is case-sensitive.
In the example below, note that the names are located in cells
A2 through A7, and we can see that all the names are setup consistently as last
name, first name, separated by one comma and one space. This is important,
because the formula will only work if the names are consistently listed.
The formula =left(A2,find(", ",A2,1)-1) will grab the last name starting with the first character until
the last character of the last name, stopping when it finds the comma and
space. The trick we use here is combining the formula “left”, with the formula “find”.
Here we want to grab the “left” characters from cell A2, and the number of
characters we want to return will be whatever character number is found
directly before the comma. Therefore instead of typing a number into the “left”
formula, we insert another formula where we “find” the comma and space, and
then subtract that character by 1 to tell the “left” formula how many
characters to display. For the example below it will return “Smith” because it
finds the “, “ at character 6, so 6-1 = 5, and the “left” formula will return
the first 5 characters of cell A2.
Here’s the result of the formula when applied to all the
cells:
Now for the formula to grab the first name: =MID(A2,FIND(", ",A2,1)+2,100).
It takes all of the characters to the right of the comma and space, using ‘mid’
and starting at the number just past the comma and space. I told it to return
the next 100 characters, that way we are sure to grab all of the characters of
the first name. Don't worry, it will actually only return the number of characters available, and won't result in any blanks.
Here’s how it looks when it’s applied to all the other cells
below:
And there you have it!
