How to Count Multiple Names Separated by Slashes in Excel
Question details
The user needs to calculate the total number of individual names across an Excel range where cells contain multiple names separated by a slash.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating the total number of delimited data items (such as names) stored within single cells across a specified data range.
- Observed behavior
- The cells contain combined text strings separated by slashes. The goal is to accurately calculate the total number of individual names, properly handling empty cells and ignoring the delimiter itself.
Ensure you are using a Microsoft 365 subscription or Excel for the web, as advanced dynamic array functions like TEXTSPLIT, REDUCE, and LAMBDA are not available in standalone versions of Excel (such as Excel 2019 or 2016).
Use REDUCE and TEXTSPLIT Formulas in Microsoft 365
The most efficient way to split and count delimited strings dynamically across a range without using helper columns.
The TEXTSPLIT function breaks the text at the slash delimiter, and COUNTA counts the resulting parts. By wrapping this in the REDUCE and LAMBDA functions, Excel iterates over the entire range to calculate the grand total automatically.
Click on the blank cell where you want the final count total to appear.
Type the formula: =REDUCE(0,TOCOL(B3:B12,1),LAMBDA(total,name,total+COUNTA(TEXTSPLIT(name,"/",,TRUE))))
Replace 'B3:B12' in the formula with the actual range containing your slash-separated names.
Press Enter. The TOCOL function with the '1' argument ensures that any completely blank cells in your range are ignored before the splitting process begins.
Use an Office Script to Count Delimited Names
Ideal for repetitive counting tasks or when building custom automation workflows in Excel on the web.
Use WPS Office to Easily Count Separated Data
WPS Office Spreadsheet provides powerful text manipulation and calculation tools. You can effortlessly count names or items separated by slashes across your dataset using classic text functions, ensuring full compatibility without needing an expensive subscription.
- 1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet containing the slash-separated names.
- 2. Apply a standard counting formula: In a blank cell next to your first data point, type =IF(ISBLANK(B3), 0, LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))+1) to count the names in that specific cell.
- 3. Sum the total range: Drag the fill handle down to calculate the count for each cell individually, then use the =SUM() function at the bottom of the new column to get the grand total.
Frequently Asked Questions
Can I count names separated by commas instead of slashes?
Yes. You simply need to replace the slash delimiter ("/") in the TEXTSPLIT or SUBSTITUTE function with a comma (",") to split and count the data based on commas.
Why does my TEXTSPLIT formula return a #NAME? error?
The #NAME? error typically occurs if you are using an older version of Excel (like Excel 2019 or 2016) that does not support the new dynamic array functions. You must use Microsoft 365 or Excel for the web to use TEXTSPLIT and REDUCE.
How do I ignore trailing slashes at the end of a cell?
In the TEXTSPLIT function, setting the fourth argument (ignore_empty) to TRUE automatically handles and ignores extra delimiters, such as a trailing slash, preventing them from being falsely counted as an additional blank name.
Is there a formula that works without Microsoft 365?
Yes. You can use a combination of classic functions available in all spreadsheet software. To count names in a single cell, use =IF(A1="","",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1). You can then use the SUM function to add up these individual cell results.
