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?action=getinfo&userid=6054
View this thread: http://www.excelforum.com/showthread.php?threadid=553526

--

Dave Peterson
.

Relevant Pages

  • Re: Semicolon problem
    ... Dim sStr As String ... For Each cell In Selection ... If ans = vbYes Then ... > Sorry guys, when i created the thread i wanted to make the wrong> semicolon BOLD, but instead it added a * at both sides of the ...
    (microsoft.public.excel.programming)
  • Re: Converting list of numbers in Excel to a string of text in Wor
    ... paste special --> values to a new cell, ... I have figured out how to add the semicolon in Excel (using a ... formual something like "A1&B1") and then copying and pasting to a new column. ... Just need to convert this list to a string with no spaces between. ...
    (microsoft.public.excel.misc)
  • Re: Counting In a Cell
    ... "carl" schrieb im Newsbeitrag ... > I have the following string in a cell. ... > semicolon. ...
    (microsoft.public.excel.worksheet.functions)
  • VB, implementing the high score within this code
    ... Private possibleAs String ... Private Function SolvePuzzleAs Boolean ... ' Calculates the possible values for all the cell ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... For Each Rng In R.Precedents.Areas ...
    (microsoft.public.excel.programming)