Data entry, excel spreadsheet
With Excel, it's easy to split up data from within individual cells into separate cells.
justplay1412/Shutterstock
  • You can split cells into columns in Excel using the “Text to Columns” tool.
  • Excel gives you two ways to split cells into columns: using delimiters, or using a manual fixed width.
  • Splitting cells into columns is a convenient way to separate first and last names, or cities and states.
  • Visit Insider’s Tech Reference library for more stories.

Microsoft Excel has lots of features that can help you manage your data.

For example, say you have a hundred cells, which are each filled with a first and last name. You can use the “Text to Columns” tool to separate these two data points (first name and last name) into two separate columns, which is great for organizing your data.

Here’s how to separate a single column of cells into two individual columns with just a couple clicks.

How to split an Excel cell into columns

There are two ways to split an Excel cell: using delimiters, or using a fixed width. These two options have some slight differences.

How to split cells into columns using a delimiter

1. In Excel, select the cell, group of cells, or entire column that has the text you want to split. It’ll need to contain two pieces of data separated by a “delimiter.” This’ll usually be a comma, period, dash, or space.

2. Click the "Data" tab and then click "Text to Columns."

How_to_split_Excel_cell_into_columns 1
Select the range of cells you want to split and select "Text to Columns" in the "Data" tab.
Dave Johnson/Insider

3. In the "Convert Text to Columns Wizard" window, choose "Delimited." If you want to base the columns on a fixed width, see the next section. 

4. Click "Next."

5. Select one or more delimiters - in other words, what Excel should use to know how to separate the columns. In a case like names or locations, it'll often be a comma. Some kinds of data imported from another spreadsheet or database might be delimited by a tab or space. You can choose more than one delimiter as well. Click "Next."

How_to_split_Excel_cell_into_columns 2
The most common delimiter is a comma, but use the one (or ones) that makes sense for your data.
Dave Johnson/Insider

6. Choose the way you want to format the column and, if you want to, use the "Destination" option to specify where in the spreadsheet the new column will appear. If you don't select this, the new split columns will overwrite the current cells. 

7. Click "Finish."

How_to_split_Excel_cell_into_columns 3
After running the wizard, your data should be separated into multiple columns.
Dave Johnson/Insider

How to split cells into columns using a fixed width

1. In Excel, select the cell, group of cells, or entire column that has the text you want to split. It doesn't need to have delimiters.

2. Click the "Data" tab and then click "Text to Columns."

3. In the "Convert Text to Columns Wizard" window, choose "Fixed width" and then click "Next."

4. Click in the ruler at the top of the "Data preview" to indicate where you want to break the text into multiple columns. After you add a break, you can drag it to reposition it. You can also add multiple breaks to create more than two columns. If you add a break you don't want, double-click it to delete it. When you're done, click "Next."

How_to_split_Excel_cell_into_columns 4
You can split cells by character rather than using a delimiter.
Dave Johnson/Insider

5. Choose the way you want to format the column and, if you want to, use the "Destination" option to specify where in the spreadsheet the new column will appear. If you don't select this, the new split columns will overwrite the current cells. 

6. Click "Finish."

Related coverage from Tech Reference:

Read the original article on Business Insider