Here is the simple procedure to Split columns in excel into multiple columns. Suppose cells in a column of your excel sheet has first name, last name and date in a single cell separated with comma or tab or semicolon or etc.,. You can easily convert them in to multiple columns.
Split columns in excel into multiple columns
- Open your excel sheet.
- Select the entire column which you want to split. You can use mouse or shift + down arrow to select entire row.
- Click on “Data” in top menu.
- Click “Text to Columns” shows that in above screenshot. You will see another window shows that in below screenshot.
- Make sure you have selected “Delimited”.
- Check preview of your selected date in the rectangular box. If any thing goes wrong, you can re select again.
- Click “Next”. You will see another window shows that in below screenshot.
- Check any one of the options available under “Delimiters“. Here, I have selected “comma” because my data was separated with “comma”. If your data is separated with semicolon or tab or space you need to select option according to your data.
- Check your preview in the “Data preview” box.
- Click “Next” if you like preview. You will see another window shows that in below screenshot.
Check “Data Preview”, you will see your out put columns. Click on first column. Check corresponding format under “Column Data Format”. In this example, first column contains first name which is a text. So I have selected “Text” under “Column Data Format”. Second column is also text. Third column is date, so I have selected “Date”. Like wise you can select data format for all columns.
Now you need to select destination fields for your output. Click on the symbol right to “Destination” field shows that in above screenshot. You will see small rectangular window shows that in below screenshot.
- Click on any cell and use shift + right arrow mark to select cells where you want to place out put data.
- You can check the selected columns in the wizard. Here I have selected cells B1, C1 and D1. Because my output has three columns. You can see selection in the wizard B1 to D1.
- Click on the same symbol in right bottom corner to close this wizard. You will go back to the main wizard.
Click “Finish” on main wizard. You will see output shows that in below screenshot.
Here I have selected cells from B1 to D1. So my original data stays in A cell. If you had selected cells from A1 to C1 you will not see original data. That will be over written by out put result.
Thanks for reading my article about Split columns in excel in to multiple columns. If you like it, do me favor by sharing it with your friends.