In a cell in Google Sheets, I have a few names of students that are comma-separated. For example, “John, Mary, Arun, Teresa.” I want to count these comma-separated words in order to find the total number of students.
I have a formula that you can use to count comma-separated words as above in Google Sheets.
You can use that formula in a single cell. Also, it would work as an array formula to return expanded results in each row.
The Non-Array Formula to Count Comma Separated Words in Google Sheets
You can use a LEN + SUBSTITUTE combo or LEN + REGEXREPLACE combo in this case.
Formula 1:
=if(A1="",,len(A1)-len(REGEXREPLACE(A1,",",""))+1)Formula 2:
=if(A1="",,len(A1)-len(SUBSTITUTE(A1,",",""))+1)You can apply either of the above formulas in cell B1.
How do the above formulas count the comma-separated words?
The formula first counts the total characters that are available in cell A1.
From this, the formula subtracts the counts of characters after removing the commas.
If there are four words, then obviously, there would be only three separator commas.
So we should add 1 to the formula output to get the actual count of the number of text strings.
The Regexreplace or the Substitute replaces the commas with blanks.
So use either of the formulas in Google Sheets to count the occurrences of words in a cell.
The Array Formula to Count Comma Separated Values in Google Sheets
Suppose you have the comma-separated words in the range A1:A.
In that case, you can use the above formula in cell B1 and drag it down to cover all the values in column A.
But I would prefer an Array Formula that can generate the result for the entire column from within 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,))It is an array formula to count comma-separated words in each row in Google Sheets.
No doubt here also you can replace the function SUBSTITUTE with the REGEXREPLACE.
=ArrayFormula(if(len(A1:A),len(A1:A)-len(REGEXREPLACE(A1:A,",",""))+1,))I want to count comma-separated dates. How to do that in Google Sheets?
You can use the above same formula for that purpose also.
The above formula is my answer to the following questions.
1. How to count comma-separated text strings in Google Sheets?
2. How to count comma-separated dates in Google Sheets?
3. Can I count comma-separated numbers in Google Sheets using a formula?
Regarding the last question, I should clarify one thing.
What’s that?
When you enter numbers as comma-separated, do as follows.
Before entering the number, select the range, here A1:A, and go to the menu Format > Number and click on “Palin Text.”
Otherwise, the formula may return an error in any cell that contains a single number.
This way (using Regex or Substitute), you can count comma-separated words in a cell/range in Google Sheets. Enjoy!
Additional 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.
- Remove Duplicates from Comma-Delimited Strings in 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.