Split Out One Cell into Two Cells
Let’s say, for example, that you have entered the full names of the contacts you have at a company in one column and you now require to have that information split into two columns, first name and last name.
Rather than re-entering all the data again, Excel can do this for you.
First highlight the column you want to split into two. Next go the ‘Data’ tab in the top ribbon and click ‘Text to Columns’ in the data tools section.
A pop up will appear asking you to confirm if Excel has selected the right split method for your data.
For this example, ‘Delimited’ is correct, as this what you would use to break up a column based on spaces, tabs or characters like commas.
Click next to choose your ‘Delimiters’ to split your column.
For this example, we want to use ‘Space’ as this is the gap between the first name and last name.
Click finish and your column of full names is now split into two columns containing the first name and last name of your contacts.
Combine Cells Easily with Formulas
So, what if you want reverse the above and combine some data into one cell? The quickest way to combine data into one cell is using the simple ‘&’ sign in a function.
Let’s take the names we have just spilt into two columns in the previous example.
We now have the first name and last name split in columns A and B. Let’s recombine those in column E where we need them to be.
First select the cell in the column where you want the combined data to go, then go to the function bar and enter then function: =B5&” “&C5
This formula tells Excel to do the following:
B5 is the location of the first name and C5 the location of the last name we want to combine.
The ‘&’ character is what creates the combination.
The two quotation marks “ “ are important, as this tells Excel that you want to add a space between the combined cell data. If we did not add these quotation marks, you would not get the space between the persons first and last name.
To then easily do this for all the rows in our example table, just drag the corner of the cell as we did in tip 2 above.
Easy.
