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

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.

Hi
Here's one way:
=LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1

--
Andy.


"Mas" <mas13@hotmail.com> wrote in message
news:u1TWo1tVFHA.2424@TK2MSFTNGP10.phx.gbl...
> 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.
>

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.


In article <OKrya4tVFHA.2420@TK2MSFTNGP12.phx.gbl>, <Andy B> wrote:

> Hi
> Here's one way:
> =LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1

One way:

If the string is in cell A1:

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



In article <u1TWo1tVFHA.2424@TK2MSFTNGP10.phx.gbl>,
"Mas" <mas13@hotmail.com> wrote:

> 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.

JE McGimpsey wrote...
>One way:
>
>If the string is in cell A1:
>
>=LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) + (LEN(A1)>0)
....

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


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-A378D6.05235812052005@msnews.microsoft.com...
> One way:
>
> If the string is in cell A1:
>
> =LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) + (LEN(A1)>0)
>
>
>
> In article <u1TWo1tVFHA.2424@TK2MSFTNGP10.phx.gbl>,
> "Mas" <mas13@hotmail.com> wrote:
>
>> 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.

Try...

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

Hope this helps!

In article <#FM01tDWFHA.2684@TK2MSFTNGP09.phx.gbl>,
"Mas" <mas13@hotmail.com> wrote:

> 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
>
>
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-A378D6.05235812052005@msnews.microsoft.com...
> > One way:
> >
> > If the string is in cell A1:
> >
> > =LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) + (LEN(A1)>0)
> >
> >
> >
> > In article <u1TWo1tVFHA.2424@TK2MSFTNGP10.phx.gbl>,
> > "Mas" <mas13@hotmail.com> wrote:
> >
> >> 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.