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]

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)

 

[kounoike]

assuming delimiter is ";" ,

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

keizi

 

[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=";")

 

[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]

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

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

 

[kounoike]

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

keizi

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

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

assuming delimiter is ";" ,

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

keizi

message news:[email protected]...

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]

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