How to Use Copilot in Excel to Split One Column into Multiple Columns
Question details
The user wants to separate a single column containing semicolon-separated items (such as mixed potluck responses) into several distinct category columns using Copilot in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Categorizing and organizing mixed string data from one column into separate, clearly defined columns for better data analysis and readability.
- Observed behavior
- Instead of manually extracting data, the user needs to leverage Copilot to automatically generate and apply formula columns that categorize the text into distinct sections.
Ensure that your data range is formatted as an Excel Table (Ctrl+T) and that you have an active Microsoft 365 subscription with Copilot enabled.
Prompt Copilot to Generate Formula Columns
Use natural language prompts to ask Copilot to analyze your column and suggest formulas that split the data into specific categories.
Copilot in Excel uses advanced AI to understand the context of your data. Instead of statically splitting text, it generates dynamic nested formulas (using IF, ISNUMBER, and SEARCH) to check for specific words and categorize the row accordingly.
Note that Copilot features are subject to gradual rollout, so interface elements may vary slightly depending on your Microsoft 365 update channel.
Click anywhere inside the Excel Table that contains your combined data column (e.g., the 'Potluck Dish' column).
Navigate to the Home tab on the Excel ribbon and click the 'Copilot' button to open the AI assistant pane.
In the chat box, type a prompt like: 'Split the Potluck Dish column into separate columns for Appetizer, Main Course, Dessert, and Side Dish.' and press Enter.
Copilot will present a preview of the suggested formula logic, such as =IF(ISNUMBER(SEARCH("Appetizer",[@[Potluck Dish]])),"Yes","No"). Review these to ensure they capture your categories correctly.
Click the 'Insert Columns' button provided by Copilot in the chat pane. This will automatically add the new calculated columns directly into your Excel Table.
Apply the Split Formulas Manually
If Copilot is currently unavailable or still rolling out to your account, you can manually use the exact formula structure that Copilot would suggest.
Split Semicolon-Separated Text into Columns for Free
If you don't have access to Copilot subscriptions, WPS Office provides a powerful, built-in 'Text to Columns' tool that effortlessly splits semicolon-separated data or any mixed text into multiple columns without the need for complex formulas.
- 1. Open your file in WPS Spreadsheets: Launch WPS Office and open the spreadsheet containing your combined data column.
- 2. Select the target column: Highlight the entire column that contains the semicolon-separated values you want to split.
- 3. Launch Text to Columns: Navigate to the Data tab on the top ribbon menu and click on the 'Text to Columns' button.
- 4. Choose the delimiter: Select 'Delimited' in the wizard step 1, click Next, and check the 'Semicolon' box in the delimiters list.
- 5. Finish splitting: Click Finish. WPS Spreadsheets will instantly separate your data into the adjacent columns based on the semicolons.
Frequently Asked Questions
Why is the Copilot button missing or greyed out in my Excel?
Copilot requires an active Microsoft Copilot Pro or Enterprise subscription. Additionally, your data must be formatted as an Excel Table, and your document must be saved to OneDrive or SharePoint. The feature is also subject to a gradual rollout.
Can Copilot split text by commas instead of semicolons?
Yes. When you write your prompt, specify the delimiter if necessary. However, Copilot's formula method (using SEARCH and ISNUMBER) looks for specific keywords within the cell, so the delimiter type often doesn't restrict its ability to categorize the data.
Will deleting the original column break the new columns created by Copilot?
Yes. Because Copilot creates formula columns that reference the original combined column (e.g., [@[Potluck Dish]]), deleting the original column will result in a #REF! error. If you need to delete the original, copy the new columns and paste them as 'Values' first.
What if Copilot's suggested formula categorizes the data incorrectly?
Before clicking 'Insert Columns', you can review the suggested formulas in the Copilot pane. If the logic is slightly off, you can reply to Copilot to refine the prompt, or you can insert the columns and manually adjust the Excel formula directly in the formula bar.
