How to split cells into columns in Microsoft Excel using the 'Text to Columns' feature
- 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.
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.
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'll 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'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."
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."
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 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."
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:
How to make a bar graph in Excel to visually represent your dataset
How to create data bars in Excel to help others visualize your spreadsheet's numbers
How to change the date format in Microsoft Excel to make the program write dates in a certain way
How to move columns in Microsoft Excel to organize your spreadsheet data
How to remove duplicates in Microsoft Excel to clean up data in individual or multiple columns