Count number of specific characters within a cell

[ceetay]

I want to count the number of commas in a cell, and use that to calculate how many separate bits of info (batch numbers) are within that cell. I have a formula (probably over-complicated) that works for two variables, but I want to count up to 3 or more. I also want it as simple as possible.

F2 contains: Y180402A02-1,YA12037.

F3 contains: HS085633

Currently my formula is:

 =IFERROR((IF(SEARCH(",",F2),((SEARCH(",",F2))-(SEARCH(",",F2))+2),"FALSE")),"1")

This will successfully tell me if F cells have 1 or 2 batch numbers. But it doesn't work for more than two batch numbers.

Any ideas?

[#2]

Hello,

the approach is to get the length of the cell, then substitute all commas in the cell with nothing, get the length of that and then subtract one from the other.  That will give you the count of commas in the cell. Add 1 to return the count of elements in the cell.

=LEN(F2)-LEN(SUBSTITUTE(F2,",",""))+1