How to Count Semicolon-Separated Names in Excel Power Pivot Charts
Question details
The user needs to count individual names that are grouped together in a single cell and separated by semicolons within a SharePoint list or Excel dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing SharePoint Project Manager and Resources columns where multiple names are stored in a single cell separated by semicolons.
- Observed behavior
- Pivot Tables and Pivot Charts treat the entire semicolon-separated string as a single distinct value, preventing the individual counting of each name.
Ensure your source data is formatted as an Excel Table or properly connected to your SharePoint list. It is highly recommended to create a backup of your original dataset before applying structural data transformations.
Transform Data Using Power Query
Use Power Query to split the semicolon-separated values, unpivot the columns, and group the data to get an accurate count for your Pivot Chart.
Power Query is the most robust tool for this task because it automates the data cleaning process. Once set up, you can simply refresh the query whenever your source data changes.
Open the Power Query Editor, right-click your SharePoint source query in the Queries pane, select "Duplicate", and rename the new query to "Pivot".
Select the "Resources" column, go to the Home tab, click "Split Column" > "By Delimiter". Choose "Semicolon" from the dropdown and click OK.
Select the newly split columns, navigate to the Transform tab, click "Format", and select "Trim" to remove any leading or trailing spaces from the names.
Highlight all the split name columns, go to the Transform tab, and click "Unpivot Columns". This will rearrange the data so all names appear vertically in a single column.
Still on the Transform tab, click "Group By". Group the data by the new name column and set the operation to "Count Rows" to calculate the occurrences of each name.
Click "Close & Load To..." on the Home tab, select "PivotChart", and build your chart using the transformed and counted data.
Count Occurrences Using the COUNTIF Formula
If you prefer not to use Power Query, you can use a wildcard COUNTIF formula to count how many times a specific name appears in the semicolon-separated column.
How to Split and Count Data in WPS Spreadsheet
WPS Spreadsheet offers powerful built-in tools like Text to Columns and Pivot Tables to help you split semicolon-separated data and count occurrences easily, without needing complex queries.
- 1. Split the data: Select the column with semicolon-separated names, go to the Data tab, and click "Text to Columns". Choose "Delimited" and set the delimiter to Semicolon.
- 2. Rearrange the data: Copy the split columns and paste them into a single column using the "Transpose" paste option if necessary, ensuring all names are in one vertical list.
- 3. Create a Pivot Table: Select your new single column of names, go to the Insert tab, and click "PivotTable".
- 4. Count the names: Drag the Name field to both the "Rows" area and the "Values" area in the PivotTable Fields pane. Ensure the Value Field Setting is set to "Count".
- 5. Insert a Pivot Chart: Select a cell inside your Pivot Table, go to the Insert tab, and choose "PivotChart" to visualize your counted names.
Frequently Asked Questions
Why doesn't a Pivot Table count semicolon-separated values automatically?
Pivot Tables treat the entire contents of a single cell as one distinct text string. If a cell contains "John; Jane", the Pivot Table reads it as a single unique value rather than recognizing John and Jane as two separate entities.
Can I use a space instead of a semicolon as a delimiter in Power Query?
Yes. When using the "Split Column by Delimiter" function in Power Query, you can select Space, Comma, Semicolon, or input a custom character based on how your source data is formatted.
Will my Pivot Chart update automatically if the SharePoint source data changes?
Yes, if your data is connected via Power Query. You simply need to right-click your Pivot Chart or Pivot Table and select "Refresh" to pull in the latest data, apply the split transformations, and update the chart.
What does the Unpivot function do in Power Query?
The Unpivot function transforms data from a wide format (multiple columns of names) into a long format (a single column of names). This is essential for grouping and counting all names together in a Pivot Table.
