Step-by-Step Guide: Dividing Data Across Several Excel Sheet Columns

Quick Links

• How to Use “Text to Columns” in Excel
• How to Use “Flash Fill” in Excel

If you start an Excel workbook by grouping data into the same cell and later decide to ungroup it, Excel has several easy functions that can split one spreadsheet column into two. Here’s how to use both “Text to Columns” and “Flash Fill.”

How to Use “Text to Columns” in Excel

Select the cells you want to split by clicking the first cell and dragging down to the last cell in the column. In our example, we’ll split the first and last names listed 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.” Delimited works great in our example, as the names are separated by commas. If the names were separated only by a space, you could select “Fixed width” instead.

Check both the “Comma” and “Space” delimiters and then the “Next” button. Delimiters are simply how the data is separated. In this case, we’re using comma and space because each cell in column A has a comma and a space separating the two. You can use any delimiter that fits your data set.

Next, we’re going to click the cell where we want to start adding the data—in this case B2—and click “Finish.” This will add the first and last names to their respective columns.

We could do this differently—for example, adding first names to column B and last names to column C. To do so, we’d highlight the first names in the wizard (notice the black highlight in the screenshot that signifies the active column) and then click the appropriate cell.

You may notice a chime and then an inability to select the cell you want to move the data into. If this happens, just click inside the “Destination” area within the wizard or add the information manually into the Destination field.

How to Use “Flash Fill” in Excel

If you only have a few names, and you don’t want to mess with the Text to Columns Wizard, you can use Flash Fill instead. This, in essence, is a smarter way to copy and paste the data into new cells.

Click inside the first cell of the appropriate column—the one named “First, in our example—and type in the first name of the first person in your dataset.

Hit “Enter” on the keyboard to move to the next cell down. From the “Home” tab on the ribbon, click “Editing” and then “Flash Fill.”

Alternatively, you can press Ctrl+E on your keyboard.

Flash Fill will try to figure out what you’re trying to accomplish—adding only the first names in this example—and paste the results into the appropriate cells.

Second, click inside the first cell of the Last column and type in the last name of the appropriate person, and hit “Enter” on the keyboard.

From the “Home” tab, click “Editing” and then “Flash Fill.” Or, use the Ctrl + E keyboard shortcut.

Once again, Flash Fill will attempt to figure out the data you want to fill into the column.

If Flash Fill doesn’t work properly, there’s always Undo (Ctrl+Z).

Also read: