In an earlier free Excel help article we explained how you could use basic formatting of text to extract data from a cell, particularly when we import text and data from other files such as .csv files. In this article we’ll delve a bit deeper and study them more closely.
Assuming we imported names of employees into our spreadsheet from a .csv file and that all our employee names are in column A of the spreadsheet let us see how we can extract first name, middle name and last name from the cell and assign them to various cells. We will be using the Find, Mid, Right and Left functions to do these tasks. Please note that all names may be of varied length, some may include punctuation such as dots and some may even not have a middle name. Our only assumption is that the order of first name, middle name and last name are first name followed by the middle name and finally the last name.
Let us first extract the first name from the cell. Now irrespective of whether there is a middle name or not the following formula, a combination using the left and find will extract the first name.
=left(A1,find(“ “,A1)-1)
A brief explanation of the above formula – the left function begins extraction of characters from the cell from the leftmost position which is ideally where the first name begins. Now how many characters must it extract? Up until the first space between the first name and the middle name is encountered or the first space between the first name and the last name in cases where there is no middle name. To let the “left” function know how many characters (i.e. the number) to extract we use the find function which will return the number after locating the first space after the last character of the first name. For e.g if the name was “John Doe” find first locates the space after the name “John” and computes the position number of that space. In this case it is 5. From this we need to subtract 1 so that the function “left” extracts only the four characters of the first name “John”. This function now will work with any first name of any length. All we need is copy the function to all the cells that use it to extract the first names.
Next let us try and extract a middle name if the name has the format “Firstname<space>Middlename<space>Lastname”. We’ll be using the MID function in conjunction with the Find.
This time the formula is going to get fairly complex but we’ll explain it in detail. The function to extract the middle name will be,
=mid(A1,find(“ “,a1)+1,find(“ “,A1,find(“ “,A1)+1)-1-find(“ “,A1))
This time the trick is to find the two spaces i.e. the first space after the first name and the second space after the middle name and then extract all the characters in between these two spaces for the middle name. We’ll break down the formula now.
The first find locates the first space i.e after the firstname and adds a 1 to it, marking the first character of the middle name. Next we need to both locate the second space i.e. after the middle name and also count the characters up to that second space. This is done using the second find that in turn uses a find first to locate and mark the first character of the middle name.
The find(“ “,A1,find(“ “,A1)+1)-1-find(“ “,A1) is to return a number to the mid function, in our case it must return 6 characters for the middle name Downey. Here the first find actually searches for the second space i.e. the one after Downey. And to start searching it begins from the find(“ “,A1)+1 position which is the “D” in Downey. Then from this we subtract -1 because we need to get rid of the second space’s position. From this we again subtract the characters till the first space after Robert to finally get just the length of the characters of the middle name. Note that with this formula you can extract any middle name of any length and of any lengths of the first and last names too. The only criterion is that the name has to have a first, middle and last name with a space before and after the middle name.
Let us just use this logic to extract the last name from the name Robert Downey Junior. This time we use the RIGHT function and the formula to extract the last name is:
=RIGHT(a1,LEN(a1)-FIND(” “,a1,FIND(” “,a1)+1))
So basically we want to extract characters starting from the rightmost character till we hit the space between the middle name and the last name. The portion LEN(a1)-FIND(” “,a1,FIND(” “,a1)+1) precisely does that. From the entire length of the name it subtracts the number till the second space. It does this by using the Find function to locate the second space. Using this formula you can extract last names of any lengths irrespective of the lengths of the first and middle names. The only constraint is that the format of the name must be first name, followed by middle name and then the last name with a space before and after the middle name.
Did this free Excel help article help? Please let us know. You can call or e-mail Christopher on the corporate office in the Irvine area of Orange COunty California to let him know. You can also suggest other free Excel help articles, videos, or downloads. We are always looking for new Excel and Access related help topics.