Split a column of data in Microsoft Excel with the Convert Text to Columns wizard. For example, a single column might contain first names and last names, and you want to have two columns, one for first names and one for last names. For addresses, you might want to split one column into four columns: street, city, state and ZIP code. Columns can be split by delimiters -- such as the space between first and last name -- or by fixed width. Fixed width is commonly used for data imports when there are set text sizes. For example, the first names all contain 10 characters, even if the last several are blank spaces, and the last names all contain 15, so you set the column to split at 10 characters.
Split a Column Using Delimiters
Video of the Day
Step 1
Select the column that contains the data by clicking on the column heading.
Video of the Day
Step 2
Click "Text to Columns" in the Data Tools group on the Data tab to launch the wizard.
Step 3
Select "Delimited" and click "Next."
Step 4
Choose the delimiter. If the delimiter is not in the list, type the delimiter in the Other field. The choices are Tab, Semicolon, Comma, Space and Other. The Data Preview pane displays a preview of the results. Click "Next."
Step 5
Choose a column format. General converts numeric values to numbers, date values to dates, and the rest to text. Text converts all data to text. Date allows you to choose a date format. "Do Not Import Column" allows you to skip the column. If you have selected more than one column, click on the next column in the Data Preview pane and choose a column format. Repeat this step until you have formatted all of the columns.
Step 6
Enter a cell value in the Destination field to select where the split data will go. If you do not choose a destination, Excel will replace the original data with the split data. Click "Finish."
Split a Column Using Fixed Width
Step 1
Select the column that contain the data by clicking on the column heading.
Step 2
Click "Text to Columns" in the Data Tools group on the Data tab to launch the wizard.
Step 3
Select "Fixed Width" and click "Next."
Step 4
Place the break by clicking on character number on the ruler above the Data Preview pane. You can move the break by clicking on the break line and dragging it to the proper location. Click "Next."
Step 5
Choose a column format. General converts numeric values to numbers, date values to dates, and the rest to text. Text converts all data to text. Date allows you to choose a date format. "Do Not Import Column" allows you to skip the column. If you have selected more than one column, click on the next column in the Data Preview pane and choose a column format. Repeat this step until you have formatted all of the columns.
Step 6
Enter a cell value in the Destination field to select where the split data will go. If you do not choose a destination, Excel will replace the original data with the split data. Click "Finish."