I have several student names separated by commas in a Google Sheets cell, such as ‘John, Mary, Arun, Teresa.’ I want to count these comma-separated names to find the total number of students.
I have a combination formula you can use to count comma-separated words in Google Sheets. You can apply this formula to a single cell, and it also works as an array formula, returning expanded results for each row.
Non-Array Formula to Count Comma-Separated Words in Google Sheets
You can use a LEN + SUBSTITUTE combo or a LEN + REGEXREPLACE combo in this case.
Let’s assume the names are in cell A1. If so, you can use one of the following formulas in cell B1 or any other empty cell in the sheet.
Formula 1:
=IF(A1="",,LEN(A1)-LEN(REGEXREPLACE(A1, ",", ""))+1)Formula 2:
=IF(A1="",,LEN(A1)-LEN(SUBSTITUTE(A1, ",", ""))+1)
How do these formulas count the comma-separated words?
The formula first counts the total number of characters in cell A1.
Next, it subtracts the number of characters remaining after removing the commas.
Since four words have three separating commas, the formula adds 1 to the result to reflect the actual number of words.
The REGEXREPLACE or SUBSTITUTE function replaces the commas with blanks. The purpose of the IF logical test is to ensure the formula returns a blank if cell A1 is empty.
So, you can use either formula in Google Sheets to count the number of words in a cell.
Array Formula to Count Comma-Separated Values in Google Sheets
Suppose you have comma-separated words in the range A1:A. In that case, you can use one of the above formulas in cell B1 and drag it down to cover all the values in column A.
However, I would prefer an array formula that can generate the result for the entire column from cell B1. For that, we can use the following array formula in cell B1:
=ArrayFormula(IF(LEN(A1:A), LEN(A1:A) - LEN(SUBSTITUTE(A1:A, ",", "")) + 1,))
This array formula counts comma-separated words in each row in Google Sheets. You can also replace SUBSTITUTE with REGEXREPLACE:
=ArrayFormula(IF(LEN(A1:A), LEN(A1:A) - LEN(REGEXREPLACE(A1:A, ",", "")) + 1,))How to Count Comma-Separated Dates in Google Sheets?
You can use the same formula above for counting comma-separated dates.
These formulas answer the following questions:
- How do I count comma-separated text strings in Google Sheets?
- How do I count comma-separated dates in Google Sheets?
- Can I count comma-separated numbers in Google Sheets using a formula?
Regarding the last question, here’s an important note:
When entering numbers or dates as comma-separated-values, first select the range (e.g., A1:A), go to the menu Format > Number, and choose ‘Plain Text.’ Otherwise, the REGEXREPLACE-based formula may return an error if any cell contains a single number or date. Additionally, it’s not advisable to use mixed data types in a column, as this can affect data manipulations.
By using REGEXREPLACE or SUBSTITUTE, you can easily count comma-separated words, dates, or numbers in a cell or range in Google Sheets. Enjoy!
Resources
- Replace Multiple Comma Separated Values in Google Sheets
- Sum, Count, Cumulative Sum Comma Separated Values in Google Sheets
- Extract Unique Values from a Comma Separated List in Google Sheets
- Vlookup and Comma-Separated Values – Google Sheets Tips
- Comma-Separated Values as Criteria in Filter Function in Google Sheets
- Split Comma-Separated Values in a Multi-Column Table in Google Sheets
- How to Remove Duplicates from Comma-Delimited Strings in Google Sheets
- How to Replace Commas within or outside Brackets in Google Sheets – Regex
- How to Compare Comma-separated Values in Google Sheets
- Validate Comma Separated Numbers within a Specific Range – Google Sheets
How would I get this to work if the cell I’m using gets changed to empty?
I’d then need it to return 0 or be blank.
Hi, Louise Prince,
I have slightly modified the formulas (except the Array Formula as it was already doing that) to meet that requirement.
Hi, Prashanth or Ethan,
I tried to use this formula over a range (eg, multiple columns) Eg,
=len(REGEXREPLACE(A1:C1, "[^,]", ""))+1. but it doesn’t work.Is there another formula I can use to do the same but across multiple columns?
Hi, Angeline,
The cell range A1:C1 in your formula should be replaced with
textjoin(",",true,A1:C1)So you can use either of the below formulas.
Based on Ethan’s solution.
=len(REGEXREPLACE(textjoin(",",true,A1:C1), "[^,]", ""))+1Based on the formula in this tutorial.
=len(textjoin(",",true,A1:C1))-len(REGEXREPLACE(textjoin(",",true,A1:C1),",",""))+1Please note that the above formulas won’t work in several rows as an array formula. For that, here is an alternative solution to count comma-separated words in multiple columns in each row.
=ArrayFormula(if(len(A1:A&B1:B&C1:C),(len(REGEXREPLACE(A1:A&if(len(B1:B),","&B1:B,"")&if(len(C1:C),","&C1:C,""), "[^,]", ""))+1),))For counting the number of comma-separated anythings in a cell: why not just –
=len(REGEXREPLACE(D2, "[^,]", ""))+1It gets rid of anything that isn’t a comma, then counts the length of the string and obviously adds 1.
Hi, Ethan,
Thanks for sharing your formula here.