15 Dec 2013

Separate first and last names in Excel

So you have got a list of full names, but require to sort it down by their first names. Excel has given a wonderful tool call "Text to columns", but it may not work all the time, if there a space or a dot or something else. Here is a simple solution to do that using formulas.

Get the first name:
Use the formula below to determine the first name
=LEFT(B2,FIND(" ",B2,1)-1)

This formula means “Start from the Left of cell B2, find a space you encounter and show all the characters before the space minus one.” This formula in the below example will give the result “Dwipen”. Since the space in “Dwipen Khwairakpam” is the 7th character, it simply starts from the first character, counts seven characters over, subtracts one (so we don’t include the space), and gives you that result.


Get the last name:
To get the last name, use the below formula
=RIGHT(B2,LEN(B2)-FIND(" ",B2,1))

This formula means "Start from the Right of cell B2, find the length of the string in the cell, find the position of the space in B2, subtract the position of space in B2 from the length of the entire string, and  return that many characters from the right.”  Confused, let me break it down for you.

The space is the 7th character in the string, and the length of “Dwipen Khwairakpam” is 18 characters; so LEN(B2) which is the total characters of the name is 18. So the formula, would return all the characters, starting from the right, what is left of 18-7 ie 11 characters from the right ie "Khwairakpam".



How about one with a middle name?
What about a name with a middle name, you would ask; say “Robert L. Stephenson”. We can do this in a couple of ways, depending on the circumstance. Considering that the middle name has a period in it, we will take advantage of it. The following formula will pull the first name and middle initial:

=RIGHT(B4,LEN(B4)-FIND(".",B4,1)-1)

Hope you will be able to figure this one out, which is similar to the last name determination.

Disqus

comments powered by Disqus