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-
- Open spreadsheet with Excel or Google Sheets
- 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.
