Counting multiple values in a single cell

[ac8038]

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

[Mallycat]

You could use =SEARCH("3",A1) where A1 is the cell with the data. Be aware however tha it will also match 31, 33 (twice) etc.

Matt

[james.igoe@gmail.com]

Depending on the version of Excel you use, you could use the Split
function into a variant, which then becomes an array. You then get the
UBound and LBound of the array. If the LBound is zero and UBound is 2,
then you have three values. If set the option, Option Base 1, in your
module, arrays are 1 based, hence the Ubound of the array will equal
the number of values in the cell.

Option Base 1

dim arrTest as variant
dim lngCount as long

arrTest = split ([cell reference], ";")

lngCount = UBound(arrTest)



ac8038 wrote:
> 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

[kounoike]

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]

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

[kounoike]

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

[Dave Peterson]

With the string you want to find (3) in A1
and the multiple values (3; 4; 7) in B1

I put this in C1:
=LEN(TRIM(";"&B1&";"))-LEN(SUBSTITUTE(TRIM(";"&B1&";"),A1,""))

And if you may search for ; (semicolon), this would be safer:
=LEN(TRIM(";"&B1&";"))-LEN(SUBSTITUTE(TRIM(";"&B1&";"),A1,""))-2*(A1=";")



ac8038 wrote:
>
> 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

[Dave Peterson]

I thought you wanted to count the number of times 3 appeared in 3; 4; 7

If that's not what you wanted, ignore this stuff.

Dave Peterson wrote:
>
> With the string you want to find (3) in A1
> and the multiple values (3; 4; 7) in B1
>
> I put this in C1:
> =LEN(TRIM(";"&B1&";"))-LEN(SUBSTITUTE(TRIM(";"&B1&";"),A1,""))
>
> And if you may search for ; (semicolon), this would be safer:
> =LEN(TRIM(";"&B1&";"))-LEN(SUBSTITUTE(TRIM(";"&B1&";"),A1,""))-2*(A1=";")
>
> ac8038 wrote:
> >
> > 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

--

Dave Peterson

[ac8038]

kounoike's solution worked really well! thanks so much though to everybody who posted up solutions this has been giving me probs for the last couple of days!