Counting multiple values in a single cell

A

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

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
 
J

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)
 
D

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

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

Dave Peterson

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

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

kounoike

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

keizi

Dave Peterson said:
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????
Click to expand...
----------------------------------------------------------------------
Click to expand...
Click to expand...
 
A

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
 
Top