+ Reply to Thread
Results 1 to 9 of 9

Counting multiple values in a single cell

  1. #1
    Registered User
    Join Date
    02-11-2004
    Posts
    3

    Counting multiple values in a single cell

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

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    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

  3. #3

    Re: Counting multiple values in a single cell


    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



  4. #4
    kounoike
    Guest

    Re: Counting multiple values in a single cell

    assuming delimiter is ";" ,

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

    keizi

    "ac8038" <[email protected]> wrote in
    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
    > ----------------------------------------------------------------------

    --
    > ac8038's Profile:

    http://www.excelforum.com/member.php...fo&userid=6054
    > View this thread:

    http://www.excelforum.com/showthread...hreadid=553526
    >



  5. #5
    Dave Peterson
    Guest

    Re: Counting multiple values in a single cell

    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

  6. #6
    Dave Peterson
    Guest

    Re: Counting multiple values in a single cell

    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

  7. #7
    Dave Peterson
    Guest

    Re: Counting multiple values in a single cell

    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" <[email protected]> wrote in
    > 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
    > > ----------------------------------------------------------------------

    > --
    > > ac8038's Profile:

    > http://www.excelforum.com/member.php...fo&userid=6054
    > > View this thread:

    > http://www.excelforum.com/showthread...hreadid=553526
    > >


    --

    Dave Peterson

  8. #8
    kounoike
    Guest

    Re: Counting multiple values in a single cell

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

    keizi

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote

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

    > >

    > ----------------------------------------------------------------------
    > > --
    > > > ac8038's Profile:

    > > http://www.excelforum.com/member.php...fo&userid=6054
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=553526
    > > >

    >
    > --
    >
    > Dave Peterson



  9. #9
    Registered User
    Join Date
    02-11-2004
    Posts
    3

    Many thanks

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1