How to split one single long column into multiple columns in Excel
If your data in Microsoft Excel is poorly structured and hosted in a single column, you can divide it into several columns. This article provides easy to understand instructions on how to split a single long column into multiple columns in Excel.
Split a column into multiple columns in Excel
Depending on the amount of the data set, a user can select one or more delimiters to divide the text into a column.
- Use the text to column conversion wizard
- Choose delimiters for your data
- Choose a destination
You can take the text in one column and divide it into multiple columns using the Convert Text to Columns Wizard.
1]Use the text to column conversion wizard
Open the Excel sheet in which you want to divide a single column into several columns.
Then go to the ‘The data“And select”Text in columns‘section.
Then in the ‘Convert text to columns’Wizard that appears on your screen, select bounded radio button and click on “Next‘ Continue.
2]Choose delimiters for your data
Here, specify the delimiters for your data. For example, comma and space.
By default, Excel will try to separate your single column data by each tab it finds. That’s fine, but you can configure the format of the column data by,
- General
- Dated
- Text and more
General converts numeric values to numbers, date values to dates, and any remaining values to text. Here we use data separated by semicolons.
If necessary, you can preview your data by going to “Data overview“Window and press”Next‘button
3]Choose a destination
Select the ‘Column data format“Or use whatever Excel has chosen for you.
Finally, specify “Destination»Where you want the split data to appear on your spreadsheet.
When done, press ‘finish“.
As you can see in the image above, the single column will be divided into several (2 columns) and the data will appear well structured and organized.