How to: calculate number of pieces when seperated by a '/' symbol

  • Thread starter Thread starter Mas
  • Start date Start date
M

Mas

Hi all,

I would like to calculate the number of pieces if it was seperated by a '/'
symbol.

i.e. 999999/88 equals two pieces
i.e. 999999 equals one piece
i.e. 999999/88/77 equals three pieces

Thank you in advanced.
 
One way:

If the string is in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) + (LEN(A1)>0)
 
Note that this will return 1 if the cell is blank.

That may be acceptable to the OP if the cell will always be populated.
 
JE McGimpsey wrote...
One way:

If the string is in cell A1:

=LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) + (LEN(A1)>0)
Click to expand...
....

Getting really picky, if "/9/8/7/" would be 5 fields, with the first
and last blank, should the result of the formula ="" be 0 or 1 fields?
If 1, then

=LEN(A1)-(LEN(SUBSTITUTE(A1,"/","")))+1-ISBLANK(A1))
 
Thanx.

I would like to expand on it a little more and not only for the formula to
refer to A1 but the who column of "A" ... i.e a continues range of numbers.

Mas
 
Try...

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"/",""))+(LEN(A1:A10)>0))

Hope this helps!
 
Back
Top