If your data in Microsoft Excel is poorly structured and accommodated in a single column, you can split it into multiple columns. This post offers easy-to-understand instructions on how to split one single column into multiple columns in Excel.
Split one column into multiple columns in Excel
To split one column into multiple columns in Excel, follow these steps:
- Switch to the Data tab.
- Click on the Text to Columns option.
- Choose the Delimited option and click the Next button.
- Select the delimiters and click the Next button.
- Choose General as the Column data format.
- Click the Finish button.
To learn more about these steps, continue reading.
1] Use Convert Text to Columns Wizard
You can take the text in one column and split it into multiple columns using the Convert Text to Columns Wizard. To get started, open the Excel Sheet in which you would like to split a single column into multiple columns.
Then, go to the ‘Data’ tab and select the ‘Text to Columns’ section.
Next, in the ‘Convert Text to Columns’ Wizard that shows up on your screen, select the Delimited radio button and click ‘Next’ to continue.
2] Choose Delimiters for your data
Here, specify the Delimiters for your data. For example, Comma and Space.
By default, Excel will attempt to separate your single-column data by each tab it finds. This is fine, but you can configure column data format by,
- General
- Date
- Text and more
General converts numeric values to numbers, date values to dates and all remaining values to text. Here, we’re using data that’s separated by semicolons.
If necessary, you can see a preview of your data by switching to the ‘Data preview’ window and hitting the ‘Next’ button.
3] Choose a destination
Select the ‘Column data format’ or use what Excel chose for you.
Finally, specify the ‘Destination’ where you would like the split data to appear on your worksheet.
When done, hit ‘Finish’.
As you can see in the image above, the single column will be split into multiple (2 columns) and the data will appear well structured and organized.
Read: How to split Text to Columns in Excel and Google Sheets
How do you separate one column data into several columns in Excel?
To separate one column data into several columns in Excel, you need to use the Text to Columns feature, which resides in the Data tab. Then, choose the Delimited option and select the Delimiters as per your requirements. For your information, you need to choose the delimiter as the character used in the spreadsheet to separate the data. For example, if you have a comma, you need to select a comma. Similarly, if you have a semicolon, you must select a semicolon. Finally, choose the data format and click the Finish button.
How do I split one column into multiple columns in Excel without delimiter?
If your spreadsheet’s text doesn’t have a delimiter, it is still possible to split one column into multiple columns in Excel. In that case, you need to use the Fixed width option instead of Delimited. When you use the Fixed width option, Excel splits every word and creates a new column for it. Having said that, if you have a text called This is sample text, you can find four columns after using the Fixed width option in the Text to Columns menu.
