If you have a spreadsheet containing stocks in a store or something similar, and you have to count the values separated by commas in a single cell, here is how you can do the job. There is a simple function that you can use to put the number of all values separated by commas in a single cell without manual work.
Suppose someone sent you a spreadsheet containing items such as products, prices, and so on. You must create a new column to indicate the number of products delivered to you in order to clarify things. For such moments, you can use this guide.
Count the number of values separated by commas in a single cell
To count the number of values separated by commas in a single cell of Excel And Google Sheetsyou must follow these steps to count the value in a single cell.
- Open a spreadsheet with Excel or Google Sheets
- Select a cell and enter a function
The detailed guide is mentioned below.
There is a simple function that does the job in a matter of moments. There is no need to install a tool or to use a third party service or software.
You must open the spreadsheet. You can use Microsoft Excel or Google Sheets to do this. Then, select a cell in which you want to display the number of all values separated by commas.
Then use the following functions
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1
Please note that the function mentioned above would display the number of cell elements A1 separated by commas. You must change the cell number to count the items in a different cell.
Although this formula works well in Excel and Google Sheets, it has a disadvantage. You must change the cell number each time. This is why using this function takes a lot of time. However, if you only have ten or twenty lines, you can use this formula to do your job.