Count number of bar codes

**Bren1987** · 10-18-2012, 01:03 PM

in cell b2 i want to place say 3 bar codes like this 3465 / 8765 / 9087 for example and these codes will change everytime and in cell c2 i want it to count how many that is. so / would separate the codes how would i count this with a formula if poss.

**Ron Coderre** · 10-18-2012, 01:10 PM

With
A1: containing a delimited list of bar codes (or is blank)...3465 / 8765 / 9087

If the number of bar codes is equal to the count of "/"+1, try this regular formula:
B1: =IF(A1<>"",LEN(A1)-SUM(LEN(SUBSTITUTE(A1,"/","")))+1,0)

Is that something you can work with?

**Bren1987** · 10-18-2012, 01:13 PM

yes for sure thank you it works exactly how i want it to. could i ask what is len?

**Ron Coderre** · 10-18-2012, 01:18 PM

For each non-blank cell in the referenced range, that formula computes the difference between the length_of_the_cell_contents and the length_with_slashes_removed and adds 1.

The length of: 123/456/789 is 11
The length of: 123456789 is 9
So...there are 2 slashes.
Therefore, there are 3 bar codes

Does that help?

**Bren1987** · 10-18-2012, 01:58 PM

ok so if i get it, it is

=SUM(LEN(C4)-LEN(SUBSTITUTE(C4,"/","")))/LEN("/")+1

sum length of c4, length substitute c4 / with blank, length +1

is that correct? i may be being a retard there but i am new to this sort of thing so trying to learn fast and use with great effects

**Ron Coderre** · 10-18-2012, 02:07 PM

Actually, I blended two approaches in my post(s).
With these values in A1:A2
3465 / 8765 / 9087
1234 / 5678 / 9012 / 3456

THIS regular formula returns the count of bar codes in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+(A1<>"")
copy that formula into B2

and this regular formula returns the count of bar codes in A1:A2
C1: =SUMPRODUCT(LEN(A1:A2)-LEN(SUBSTITUTE(A1:A2,"/",""))+(A1:A2<>""))

For the sample data
B1 returns: 3
B2: returns: 4
and C1 returns: 7

I hope that helps.

This regular formula counts the number of bar codes in A1:A5