Counting multiple values in a single cell

assuming delimiter is ";" ,

=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1

keizi

"ac8038" <ac8038.29p00z_1150793101.5207@excelforum-nospam.com> wrote in
message news:ac8038.29p00z_1150793101.5207@excelforum-nospam.com...
>
> I have a cell which contains multiple values set out like this:
>
> 3; 4; 7
>
> I would like to be able to count the number of values (3) in this
cell.
> Does anybody know how I could do this????
>
>
> --
> ac8038
> ----------------------------------------------------------------------
--
> ac8038's Profile:
http://www.excelforum.com/member.php...fo&userid=6054
> View this thread:
http://www.excelforum.com/showthread...hreadid=553526
>

And if the OP didn't want to include the space characters:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),";",""))+(A1<>"")



kounoike wrote:
>
> assuming delimiter is ";" ,
>
> =LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1
>
> keizi
>
> "ac8038" <ac8038.29p00z_1150793101.5207@excelforum-nospam.com> wrote in
> message news:ac8038.29p00z_1150793101.5207@excelforum-nospam.com...
> >
> > I have a cell which contains multiple values set out like this:
> >
> > 3; 4; 7
> >
> > I would like to be able to count the number of values (3) in this
> cell.
> > Does anybody know how I could do this????
> >
> >
> > --
> > ac8038
> > ----------------------------------------------------------------------
> --
> > ac8038's Profile:
> http://www.excelforum.com/member.php...fo&userid=6054
> > View this thread:
> http://www.excelforum.com/showthread...hreadid=553526
> >

--

Dave Peterson

Thank you for your correction, Dave. i never thought of that.

keizi

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4497DDC0.5F1BE9F9@verizonXSPAM.net...
> And if the OP didn't want to include the space characters:
>
> =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),";",""))+(A1<>"")
>
>
>
> kounoike wrote:
> >
> > assuming delimiter is ";" ,
> >
> > =LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1
> >
> > keizi
> >
> > "ac8038" <ac8038.29p00z_1150793101.5207@excelforum-nospam.com> wrote
in
> > message news:ac8038.29p00z_1150793101.5207@excelforum-nospam.com...
> > >
> > > I have a cell which contains multiple values set out like this:
> > >
> > > 3; 4; 7
> > >
> > > I would like to be able to count the number of values (3) in this
> > cell.
> > > Does anybody know how I could do this????
> > >
> > >
> > > --
> > > ac8038
> >
> ----------------------------------------------------------------------
> > --
> > > ac8038's Profile:
> > http://www.excelforum.com/member.php...fo&userid=6054
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=553526
> > >
>
> --
>
> Dave Peterson