In Google Sheets, there may be instances when you have a cell containing a list of words separated by commas. For example, you might have a list of student names like “John, Mary, Arun, Teresa.” To determine the total number of students, you need to count these comma-separated words. Luckily, there is a formula that can help you achieve this in Google Sheets.
The Non-Array Formula to Count Comma Separated Words
To count the number of comma-separated words in a single cell, you can use either of the following formulas:
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 these formulas in cell B1. But how exactly do these formulas count the comma-separated words?
The formulas start by calculating the total number of characters in cell A1. Then, they subtract the count of characters after removing the commas. Since there are only three separator commas if there are four words, we need to add 1 to get the actual count of text strings.
The REGEXREPLACE or SUBSTITUTE function is used to remove the commas from the cell. By using either of these formulas, you can accurately count the occurrences of words in a cell in Google Sheets.
The Array Formula to Count Comma Separated Values
If you have a range of comma-separated words in column A, you can use an array formula to calculate the word count for the entire column starting from cell B1. Here’s the array formula you can use in cell B1:
=ArrayFormula(if(len(A1:A),len(A1:A)-len(SUBSTITUTE(A1:A,",",""))+1,))
This array formula allows you to count the comma-separated words in each row of column A in Google Sheets. You can simply drag this formula down to cover all the values in column A.
Of course, you can also replace the SUBSTITUTE function with REGEXREPLACE if you prefer using regular expressions.
Counting Comma-Separated Dates or Numbers
If you want to count comma-separated dates or numbers in Google Sheets, you can use the same formulas mentioned above. Simply apply the formulas to the respective range or cell.
However, when entering numbers as comma-separated values, make sure to format the range (e.g., A1:A) as “Plain Text” before entering the numbers. To do this, select the range, go to the menu “Format” > “Number,” and click on “Plain Text.” This step is necessary to avoid any errors in cells containing a single number.
And that’s it! By using either the REGEXREPLACE or SUBSTITUTE function, you can easily count comma-separated words, dates, or numbers in Google Sheets.
To explore more ways to enhance your Google Sheets skills, visit Crawlan.com. Enjoy counting those comma-separated words with confidence!
