Count number of comma separated values in single cell in Excel and Google Sheets

If you have a spreadsheet containing the stocks in a shop or something similar, and you need to count the comma-separated values in a single cell, here is how you can get the job done. There is a simple function that you can use to put the number of all comma-separated values in a single cell without any manual work.

Let’s assume that someone has sent you a spreadsheet that has items like products, prices, etc. You need to make a new column to mention how many products have reached you so that you can clear things up. For such times, you can use this guide.

Count number of comma-separated values in a single cell

To count the number of comma-separated values in a single cell in Excel & Google Sheets, you need to follow these steps to count the value in a single cell-

1. Open spreadsheet with Excel or Google Sheets
2. Select a cell and enter a function

There is a simple function that does the job within moments.

You need to open the spreadsheet. You can use Microsoft Excel or Google Sheets to do that. After that, select a cell where you want to display the number of all comma-separated values.

Then, use the following function-

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1

Please note that the function mentioned above would display the number of all comma-separated items of A1 cell. You need to change the cell number to count the items of a different cell.

Although this formula works smoothly in Excel and Google Sheets, there is a drawback.

You need to change the cell number every time. That is why using this function is quite time-consuming. However, if you have only ten or twenty rows, you can use this formula to get your job done.

Read: How to count Nonblank Cells in Excel.