1. Open Microsoft Excel on your computer and open the worksheet containing the text you want to split.
2. Select the column or cells containing the text you want to split.
3. Go to the "Data" tab in the Excel ribbon at the top of the window.
4. In the "Data Tools" group, click on the "Text to Columns" button. This will open the "Convert Text to Columns Wizard" dialogue box.
5. In the wizard, you'll be presented with two options for splitting the text: "Delimited" and "Fixed width." Choose the appropriate option based on how your text is structured. If specific characters or symbols separate your text, select "Delimited." If your text has a consistent width for each field, select "Fixed width." We'll assume you want to split the text based on a delimiter for this example.
Click on the "Next" button to proceed.
6. In the next step, you'll need to specify the delimiter that separates the text into different columns. Common delimiters include commas, tabs, semicolons, spaces, etc. Select the delimiter that matches your data. You can also preview the changes in the "Data Preview" section. You can adjust the delimiter settings by selecting/deselecting options in the "Delimiters" section or by typing a custom delimiter in the "Other" field.
Once you've selected the delimiter and configured the settings, click on the "Next" button.
7. In the final step, you can choose the data format for each column. By default, Excel applies the "General" format, which attempts to recognise the appropriate format automatically. However, if your data contains specific date formats, numbers, or text, you can select the respective column and choose the proper format from the options provided. You can skip columns you don't want to split by selecting them and choosing the "Do not import column (skip)" option.
After configuring the column formats, click on the "Finish" button.
8. Excel will then split the selected text into separate columns based on the chosen delimiter or fixed width settings. The original column will be replaced with the split columns.
Note: If you want to keep the original text intact and have the split text in separate columns, ensure you have enough empty columns adjacent to the original column to accommodate the split data.
To split text in Microsoft Excel using the "Text to Columns" feature, you can follow these steps:
Watch Youtube Video
1. Open Microsoft Excel on your computer and open the worksheet containing the text you want to split.
2. Select the column or cells containing the text you want to split.
3. Go to the "Data" tab in the Excel ribbon at the top of the window.
4. In the "Data Tools" group, click on the "Text to Columns" button. This will open the "Convert Text to Columns Wizard" dialogue box.
5. In the wizard, you'll be presented with two options for splitting the text: "Delimited" and "Fixed width." Choose the appropriate option based on how your text is structured. If specific characters or symbols separate your text, select "Delimited." If your text has a consistent width for each field, select "Fixed width." We'll assume you want to split the text based on a delimiter for this example.
Click on the "Next" button to proceed.
6. In the next step, you'll need to specify the delimiter that separates the text into different columns. Common delimiters include commas, tabs, semicolons, spaces, etc. Select the delimiter that matches your data. You can also preview the changes in the "Data Preview" section. You can adjust the delimiter settings by selecting/deselecting options in the "Delimiters" section or by typing a custom delimiter in the "Other" field.
Once you've selected the delimiter and configured the settings, click on the "Next" button.
7. In the final step, you can choose the data format for each column. By default, Excel applies the "General" format, which attempts to recognise the appropriate format automatically. However, if your data contains specific date formats, numbers, or text, you can select the respective column and choose the proper format from the options provided. You can skip columns you don't want to split by selecting them and choosing the "Do not import column (skip)" option.
After configuring the column formats, click on the "Finish" button.
8. Excel will then split the selected text into separate columns based on the chosen delimiter or fixed width settings. The original column will be replaced with the split columns.
Note: If you want to keep the original text intact and have the split text in separate columns, ensure you have enough empty columns adjacent to the original column to accommodate the split data.