Here is the simple procedure to Split columns in excel into multiple columns. Suppose cells in a column of your excel sheet have a first name, last name, and date in a single cell separated with comma or tab or semicolon or etc.. You can easily convert them into 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 a mouse or shift + down arrow to select the entire row.
- Click on “Data” in the top menu.
- Click “Text to Columns” shows that in the above screenshot. You will see another window shows that in the below screenshot.
- Make sure you have selected “Delimited”.
- Check the preview of your selected date in the rectangular box. If anything goes wrong, you can reselect again.
- Click “Next”. You will see another window shows that in the 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 options according to your data.
- Check your preview in the “Data preview” box.
- Click “Next” if you like the preview. You will see another window shows that in the below screenshot.
Check “Data Preview”, you will see your output columns. Click on the first column. Check the corresponding format under “Column Data Format”. In this example, the first column contains the first name which is a text. So I have selected “Text” under “Column Data Format”. The second column is also text. The third column is the date, so I have selected “Date”. Likewise, you can select a 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 the above screenshot. You will see a small rectangular window shows that in the below screenshot.
- Click on any cell and use shift + right arrow mark to select cells where you want to place output 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 the selection in the wizard B1 to D1.
- Click on the same symbol in the right bottom corner to close this wizard. You will go back to the main wizard.
Click “Finish” on the main wizard. You will see the output shows that in the 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 the original data. That will be overwritten by the output result.
Thanks for reading my article about Split columns in excel into multiple columns. If you like it, do me favor by sharing it with your friends and follow WhatVwant on Facebook and Twitter for more tips.