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

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.

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