[Office] How To Count Comma Separated Values In A Single Cell In Excel?

How To Count Comma Separated Values In A Single Cell In Excel?

If cell content is separated by comma in a single cell, such as “A1, A2, A3, A4, A5”, and you want to count the total number of comma separated values in this cell, what can you do? In this case, the total comma separated values should be 5. In this article, we will show you two methods of counting comma separated values in a single cell in Excel.

1. Select a blank cell to output the result.

2. Type the below formula into it and press the Enter key. Selec the result cell and then drag its Fill Handle down to get all results.

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