Function Problems

How to Count Employee Names Stored in Single Excel Cells

Amos Gikunda Sep 28, 2026 869 views

Question details

The user needs to extract, separate, and count individual employee names that are combined in single Excel cells and separated by semicolons, to determine how many overtime assignments each person has.

How to Count Employee Overtime Names Stored in Excel Cells

Product: Excel
Device & OS: not provided
Scenario: Creating an overtime tracking table from unstructured raw data where multiple employees assigned to a shift are recorded within the same cell.
Observed behavior: Employee names are grouped together with semicolons in individual cells, preventing standard counting functions like COUNTIF from accurately counting each person's individual occurrences.

Before you start

Verify if your version of Excel supports Dynamic Array functions like TEXTSPLIT and TEXTJOIN, or ensure you have access to the Data tab to utilize Power Query for advanced text manipulation.

Solution 1Recommended

Use Power Query to Split and Count Delimited Names

Power Query is the most robust and highly recommended method for splitting text by delimiters, unpivoting data, and automatically counting occurrences for reporting.

Power Query allows you to create a repeatable process. When new overtime data is added to your source table, you can simply refresh the query to update your counts without rewriting formulas.

Load data into Power Query

Select your source data range containing the combined names, go to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Split the column by delimiter

Select the column with the names, navigate to 'Home' > 'Split Column' > 'By Delimiter'. Choose 'Semicolon' as your delimiter. Expand the 'Advanced options' section and select 'Rows' so each name is placed on a new line.

Trim excess spaces

To ensure names are counted correctly (e.g., 'John' and ' John' are treated as the same), select the newly split column, go to 'Transform' > 'Format' > 'Trim'.

Group and count the names

Navigate to 'Home' > 'Group By'. Group by the name column, set the New column name to 'Overtime Count', and choose 'Count Rows' as the operation. Click 'OK'.

Load the results back to Excel

Click 'Close & Load' on the Home tab. The cleaned and counted list of employee overtime assignments will be generated in a new worksheet as a formatted table.

Use Power Query to Split and Count Delimited Names

Data Updates: Whenever you add new overtime entries to your original table, simply right-click the green Power Query result table and select 'Refresh' to instantly update the counts.

Solution 2

Use Dynamic Array Formulas (TEXTSPLIT & TEXTJOIN)

For Microsoft 365 users, combining TEXTSPLIT and TEXTJOIN provides a quick, formula-based approach to extract and reshape semicolon-separated data without leaving the worksheet.

Efficient Data Processing

Split and Summarize Delimited Data Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful Text-to-Columns features, advanced formula support, and intuitive PivotTables to seamlessly split grouped cell data and calculate accurate overtime frequencies without complex setup.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your workbook, and select the column containing the combined employee names.
2. Use Text to Columns: Go to the 'Data' tab and click on 'Text to Columns'. Choose 'Delimited', select 'Semicolon' as the delimiter, and click Finish to separate the names into adjacent columns.
3. Stack the data into one column: Copy the separated names from multiple columns, right-click a blank cell, and use 'Paste Special' > 'Transpose' to align them vertically if needed.
4. Insert a PivotTable: Select your newly stacked name column, navigate to the 'Insert' tab, and click 'PivotTable'.
5. Count the assignments: In the PivotTable fields pane, drag the 'Employee Name' field into both the 'Rows' area and the 'Values' area. The Values area will default to 'Count of Name', instantly showing each employee's overtime total.

Built-in Text-to-Columns wizard for quick data separationFully compatible with Microsoft Excel formats (.xlsx and .xls)Easy-to-use PivotTables for instant data summarization and countingLightweight, fast, and free to use for daily office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why are there extra spaces before some employee names after I split them?

Extra spaces occur if the original data had spaces after the semicolons (e.g., 'John; Jane' instead of 'John;Jane'). You can clean this up by wrapping your formulas with the TRIM() function or by using the Trim formatting option in Power Query.

Can I use the Text to Columns feature instead of complex formulas?

Yes, you can use the Text to Columns feature under the Data tab to split the semicolon-separated names into multiple columns across the spreadsheet. However, to count them efficiently with a PivotTable, you will subsequently need to copy and paste those columns into a single vertical list.

What if my version of Excel doesn't support the TEXTSPLIT function?

If you are using an older version of Excel (like Excel 2016 or 2019) that lacks Dynamic Array functions like TEXTSPLIT, it is highly recommended to use the Power Query method. Alternatively, you can use a VBA macro to split and restructure the text into separate rows.

Excel & Data Problems