How to Split a Column of Data in MS Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Increase your spreadsheet's flexibility by listing everyone's first names and last names in separate fields.
Image Credit: Pixland/Pixland/Getty Images

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.

Advertisement

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.

Advertisement

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

Advertisement

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.

Advertisement

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

Advertisement

Split a Column Using Fixed Width

Step 1

Select the column that contain the data by clicking on the column heading.

Advertisement

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

Advertisement

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.

Advertisement

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

Advertisement

Advertisement

references