How to Count Comma-Separated Words in a Cell in Google Sheets

Published on

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)
Counting comma-separated words in a cell in Google Sheets

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,))
Array formula to count comma-separated words in Google Sheets

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:

  1. How do I count comma-separated text strings in Google Sheets?
  2. How do I count comma-separated dates in Google Sheets?
  3. 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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

6 COMMENTS

  1. 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), "[^,]", ""))+1

      Based on the formula in this tutorial.

      =len(textjoin(",",true,A1:C1))-len(REGEXREPLACE(textjoin(",",true,A1:C1),",",""))+1

      Please 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),))

  2. For counting the number of comma-separated anythings in a cell: why not just –

    =len(REGEXREPLACE(D2, "[^,]", ""))+1

    It gets rid of anything that isn’t a comma, then counts the length of the string and obviously adds 1.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.