Excel Formula To Count The Number Of Items In A Cell Separated By Commas


Excel Formula To Count The Number Of Items In A Cell Separated By Commas

Are you looking for a way to quickly count the number of items in a cell that are separated by commas? If so, then you’ll be glad to know that there’s a (not so) simple Excel formula that can do just that.

If you’re working on a spreadsheet and short on time, here is the formula that will find the number of items separated by a comma in cell A1. If you have more time, read on for a more detailed explanation.

=LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1

How The Formula Works

The formula works by creatively using just two Excel functions: LEN and SUBSTITUTE.

LEN counts letters, numbers, characters, and all spaces in a particular cell.

SUBSTITUTE replaces specific text within a text string.

The formula works by first finding the length of the cell using the LEN function, then using the SUBSTITUTE function to remove all of the commas, and finally finding the length of the new cell without commas.

Subtracting the length of the cell without commas from the original length of the cell and adding 1 will give you the number of items in the cell separated by commas.

Here is a simple example:

Excel Formula To Count The Number Of Items In A Cell Separated By Commas

 

 

 

 

Also Read: Excel Formula to Count the Number of Words in a Cell

Recent Posts