Power Query Problems

How to Create Columns from Unique Comma-Separated Values in Excel

WPS Editor Oct 7, 2026 868 views

Question details

The user needs to split a column of comma-separated titles into individual unique columns and populate each person's row with a 1 or 0, ensuring that overlapping titles are not incorrectly matched.

How to Create Columns from Unique Comma-Separated Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Transforming unstructured comma-separated roles into a boolean matrix for data analysis or reporting.
Observed behavior: Standard search functions incorrectly match overlapping text (e.g., finding 'Manager' inside 'Senior Manager'), resulting in false positive flags.

Before you start

Before proceeding, ensure your comma-separated text data does not contain inconsistent spacing, or prepare to use the trim function to clean your data first.

Solution 1Recommended

Extract and Pivot Values using Power Query

Power Query is the most robust method to automatically split, clean, and pivot delimited data into a matrix format.

Load data into Power Query

Select your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Split the delimited column

Select your Title column, click 'Split Column' by Delimiter, choose comma, and under the Advanced options, choose to split into Rows.

Trim the resulting values

To remove any trailing or leading spaces created by the commas, select the split column and go to Transform > Format > Trim.

Pivot the column data

Duplicate the trimmed column. Select the original column, navigate to the Transform tab, and click 'Pivot Column'. Set the Values Column to your duplicated column and select 'Count (All)' or 'List.Count' as the Aggregate Value Function.

Extract and Pivot Values using Power Query

Solution 2

Use a Delimiter-Aware SEARCH Formula

If you prefer using formulas instead of Power Query, you can use a combination of IF, ISNUMBER, and SEARCH with appended delimiters to create an accurate boolean matrix.

Efficient Data Processing

Use WPS Spreadsheet to Process Boolean Matrices

You can achieve this exact data separation and boolean matching seamlessly using WPS Spreadsheet's robust formula engine, which perfectly supports complex search and conditional functions.

1. Open dataset in WPS: Launch WPS Spreadsheet and open your existing data file.
2. Set up matrix headers: Type out your unique category headers across the top row adjacent to your raw data.
3. Apply delimiter-aware formula: Paste the precise SEARCH and IF formula into the first cell of your matrix.
4. Auto-fill rows and columns: Use the drag-to-fill cursor crosshair to instantly populate the 1s and 0s across all relevant rows.

Fully compatible with Microsoft Excel standard formulas and .xlsx files.Flawlessly executes IF, ISNUMBER, and SEARCH functions for exact text matching.Lightweight and fast, handling large datasets and complex matrices without lag.Completely free to use with an intuitive, tabbed interface for easier multitasking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return 1 for 'Manager' when the text is 'Senior Manager'?

Standard SEARCH functions look for the exact string sequence anywhere in the text. Since the word 'Manager' is contained within 'Senior Manager', it triggers a match. Wrapping both the search term and the text in delimiters (like commas and spaces) prevents these partial matches.

Can I use the 'Text to Columns' feature instead?

The 'Text to Columns' feature will separate the delimited text into adjacent columns, but it will not dynamically create a structured matrix of unique headers populated with 1s and 0s. Power Query or matrix formulas are required to achieve that specific layout.

How do I handle inconsistent spaces after commas in my dataset?

If your dataset has irregular spacing, you can wrap your text reference in the TRIM function within your formula. If using Power Query, apply the Trim formatting option to your column right after the split step to clean the data before pivoting.

Excel & Data Problems