How to separate data in Excel

You may want to separate data in an excel cell. It is referred to as splitting a cell. You can split or parse a cell, column, or row in such a way that you have your data in different places. Luckily, excel offers its users an easier way to split and separate data.

Method 1; how to separate data in excel using the Convert Text to Columns Wizard

1. Open your excel spreadsheet containing the data you want to separate.

2. Highlight the column, row, or cell that contains the combined data you want to separate. To do this, click on the letter directly above the column.

3. On the main menu, click on the Data tab.

4. Under the Data Tools group, click on the option 'Text to Columns' to display a wizard box.

5. In the Convert Text to Column wizard box, follow the steps below;

  • Step 1, select the option 'Delimited' then click 'Next.'

  • Select the delimiter or delimiters you want to appear within your data. Note, a delimiter is a symbol that separates the data you wish to separate. For example, a comma is a delimiter that can be used to separate values or texts in excel. Check on the Data preview section to see how your content will look like.
  • Next, check or select the box next to 'Treat Consecutive delimiters as one.'
  • Click Next.
  • Under the field 'Column data format,' select the option 'General.'
  • Afterward, click on the red arrow icon found at the far right of the Destination text box. Doing this will redirect you to your worksheet where you will select to keep your split text.
  • Highlight the columns you wish to contain your separated data. To do this, click on the letters directly above the columns. You can also manually highlight your columns by clicking and dragging to select the cells that you wish to store your separated data.
  • Next, click on the spreadsheet icon to expand your wizard box.

6. Click on the Finish button. After doing so, your data will be separated.

Using the Flash Fill function to separate

You can use the Flash Fill function in Excel when you do not want to mess with the Text to Colons Wizard box. It is easier to use it if you only have fewer names. In this method, you will copy and paste your data into new cells, rows, or columns to separate it.

1. First, click inside the first cell of the column and type in the first name of the first person in the excel dataset.

2. Hit Enter on your keyboard to move to the next cell.

3. On the main menu, go to the Home tab.

4. Click on the Editing option then, click Flash Fill. The function will try to decipher your command and paste the results on the other cells (in our case, adding the first names).

5. To type in the last names in a different column, click inside the first cell of your last column and type in the last name. Hit Enter.

6. Go to the Home tab on the main menu. Click on the Editing option.

7. Next, click on Flash Fill to fill out the remaining blank cells.

Leave a Comment