Split and Join Cells in Excel

Description
One of the most common issues is when you have a series of first and last names in excel that you would like to split or join them.


Check the video and the downloadable file.


We will show you how with various methods. Choose the method that corresponds to your needs.


Let us go to the tab SPLIT. 
We will use the flash fill method to split the the cells contents in column B into first and last names in columns C and D respectively.
To use the flash fill method type one or two examples in the corresponding cells. When you see the Excel prompting you to autofill the rest of the names press ENTER.

Another method is to write one example, here we will write Rubio and then press the shortcut CONTROL plus E.



Let us move to the second method. Go to DATA, then TEXT TO COLUMNS, select DELIMITED, press NEXT, tick only the SPACE box and press NEXT and FINISH.


The third method consist of using a formula. The advantage of the formula is that it is dynamic and as such you won't need to repeat the process if your underlying data change.
So first we will use the formula FIND to find the empty space between the first and last name, and copy the formula to the right.
Then we will use the function MID.
To get the last name, we will use the RIGHT function and for the number of characters we will use the LEN function.


Now let us visit tab JOIN. Now we want to perform the reverse task, we have the first and last name in separate cells and we want to join these 2 cells into 1.
We will firstly use the flash fill method. We write an example in cell D3 and then we press CONTROL plus E.


The second method consists of using the CONCATENATE or CONCAT function. 


Another way to do this is to use the TEXT JOIN function.



Finally we can use the ampersand sign.