How to split cells into columns in Microsoft Excel using the 'Text to Columns' feature

justplay1412/ShutterstockWith Excel, it’s easy to split up data from within individual cells into separate cells.
  • 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 organising 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.

Delimited vs Fixed Width columns

  • The Delimited option will split your data using commas, spaces, periods, dashes, or other formatting marks that you might have in a single cell. For example, if you’ve written out someone’s name as “Smith, John,” it will separate Smith and John into separate columns.
  • The Fixed Width option lets you manually pick where you want Excel to split the data. For example, if you have a list of names, you can tell Excel to split the data so one column has the first letter of everyone’s name, and the second column has the rest of the name.

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 will 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.”

Dave Johnson/InsiderSelect the range of cells you want to split and select ‘Text to Columns’ in the ‘Data’ tab.

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 will 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.”

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

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.”

Dave Johnson/InsiderAfter running the wizard, your data should be separated into multiple columns.

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.”

Dave Johnson/InsiderYou can split cells by character rather than using a delimiter.

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:

Business Insider Emails & Alerts

Site highlights each day to your inbox.

Follow Business Insider Australia on Facebook, Twitter, LinkedIn, and Instagram.