Sometimes it is useful to be able to “split” or extract text from a cell into various cells in Excel. Often times we get a list of names in a single column configuration, and in reality we need to have first and last names in their own individual columns. We could try to do it manually, but it could become a very long and tedious task.
Excel has a built in function to make this possible, where it will be able to extract the required information and place it in a separate cell. Helping us save time and labor intensive work.
We have a workbook with a full names in a single cell, we will the MID() function
We will have to use a nested function, meaning a function within a function, in our case, the =MID() function, will be the function to start extracting text, we just have to plug in several parameters for it to start looking for the text to extract. The parameters needed will be, Text which refers to the cell where the text is contained, in our case, A2, start_num, this will tell the function the initial position to start looking for text, in our case 1, final parameter, num_chars, tells the function the number of characters to extract, in our case, we will use another function for this. The FIND() function, is the function that will do this for us, find the number of characters to extract from the cell. We need to input the following parameters, for the function to start working, first will input “” “”, double quotes with a blank space between, this tells the function, to look for characters till it comes to a white space between words, Javier and Andres, second we enter the cell, A2, and lastly we enter the initial position to start looking to the text, in our case the first character, 1.
Complete formula: =MID(A2,1,FIND(“ “,A2,1))
To start extracting the second name from the cell the process will vary a little, this time we will not tell it to stop at the first white space, but the second, for which we will use a third function, which is LEN(), will help us find the length of characters in a cell.
Full formula is:
=MID(A2,FIND(” “,A2),FIND(” “,A2,LEN(B2)+1)-LEN(B2))
Slight difference in the parameters that are used for the function is, again we are looking for the white spaces, but this time we are going to start where we left off with the first name, this can be accomplished with the function LEN(B2)+1, the +1 indicates that it is to start a character after, we have to keep in mind that there is a white space after the first name.
Then to extract, we indicate that we are going to extract the quantity of characters that we counted, minus the length of the first name, with this function:
To extract the first last name, we will use the same function we used to get the middle name, only this time we will start searching where the middle name ends, for such we will use this function
For the second last name, the function will have to vary slightly again just in that in reality it is not necessary to define how many characters we wish to extract, since after the last name, there are no more characters.
Function to use will be this one:
Here we have to keep in mind, that we are no longer looking for the white space between the characters we are just going to indicate that the extraction will begin where the last name ends, this is done with this part of the function:
And as before, it is not necessary to know how many characters we are going to extract.
Having entered the necessary function to extract each one of the first and last names, we proceed to copy the formulas in the rest of the cells to fill out the rest of the workbook in the following manner.
To copy functions, highlight cells that contain functions only, first row of extracted names, right click and chose copy, now, highlight the rest of the empty cells for the rest of the names, and right click and click on paste.
Extraction will not work if last names are composed of two or more words, example: Juan del Rosario Perez de Castro