Home

Count number of specific characters within a cell

Highlighted
ceetay
Occasional Visitor

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?

1 Reply
Highlighted

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

 

 

 

 

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Count until
MBelshaw in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies