If you start an Excel workbook by grouping data in the same cell and later decide to ungroup, Excel has several simple functions that let you split a spreadsheet column in two. Here’s how to use both Text to Columns and Quick Fill.
How to Use “Text to Columns”
; in Excel
Select the cells you want to split by clicking the first cell and dragging it to the last cell in the column. In our example, we split the first and last name in column A into two different columns, column B (last name) and column C (first name.)
Click the “Data” tab at the top of the Excel ribbon.
Click the “Text to Columns” button in the Data Tools section.
In the Convert Text to Columns wizard, select “Delimited” and then click “Next.” Separators work fine in our example, because the names are separated by commas. If the names were only separated by a space, you could select “Fixed Width” instead.
Check both the “Comma” and “Space” separators and then the “Next” button. Separators are simply how the data is separated. In this case, we’ll use a comma and a space, because each cell in column A has a comma and a space separating the two. You can use any separator that matches your data set.
Then we click on the cell where we want to add the data (in this case B2) and click on ‘Finish’. This will add the first and last name to their respective columns.
We could do this differently, for example by adding first names to column B and surnames to column C. To do this, we highlight the first names in the wizard (note the black mark in the screenshot indicating the active column) and then click on the appropriate cell.
You may hear a beep and then the inability to select the cell where you want to move the data. If this happens, simply click in the “Destination” area of the wizard or manually add the information to the Destination field.
How to use “Flash Fill” in Excel
If you only have a few names and you don’t want to mess around with the Text to Columns wizard, you can use Flash Fill instead. This is essentially a smarter way to copy and paste the data into new cells.
Click in the first cell of the appropriate column – the column labeled First, in our example – and type the first person’s first name in your dataset.
Press “Enter” on the keyboard to move to the next cell. On the “Home” tab on the ribbon, click “Edit” and then “Quick Fill”.
You can also press Ctrl + E on your keyboard.
Flash Fill will try to figure out what you are trying to achieve – just add the first names in this example – and paste the results into the appropriate cells.
Second, click in the first cell of the last column and type in the correct person’s last name and press “Enter” on the keyboard.
On the ‘Home’ tab, click ‘Edit’ and then ‘Quick Fill’. Or use the keyboard shortcut Ctrl + E.
Again, Flash Fill will try to find out the data you want to fill in the column.
If Flash Fill doesn’t work properly, there is always Undo (Ctrl + Z).