Home

Count number of specific characters within a cell

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

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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies