Counting the number of items in a cell

[gaijin2004]

A simple question for you guys, I just don’t know how to enter this in formula. Say in a certain cell A1 containing numbers 1 15 25 4, the formula counts how many numbers contained in that cell A1. The formula should return 4 or in case the cell contains 15 2 8 10 100 59 then it should return 6 and so on. The numbers provided in the cell is either separated by comma or spaces, arranged in any order(ascending or descending). Thank you very much in advance!

 

[GregLocock]

You'll have to parse the cell contents, unless there is a pretty sophisticated string analysis function.

You may have some joy using SEARCH and SUBSTITUTE to look for your delimiters, but you'll have to watch out for double spaces etc.



Cheers

Greg Locock

 

[johnwm]

A little VBA will get you started. Put a Command button on the page and paste this code into the sheet code page:

Private Sub CommandButton1_Click()
a = ActiveSheet.Cells(1, 1).Value
MsgBox UBound(Split(a, " ")) + 1
End Sub

As Greg says, you will have to remove double spaces first, but this will get you started. You can obviously trigger this code however you want

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[prex]

Try the following Visual Basic Function:

[tt]Public Function NumberCount(cell)
Dim i1 As Integer, i2 As Integer
i1 = 0
For Each Elem In Split(cell, " ")
If Len(Elem) Then i1 = i1 + 1
Next
i2 = 0
For Each Elem In Split(cell, ",")
If Len(Elem) Then i2 = i2 + 1
Next
If i1 > i2 Then
NumberCount = i1
Else
NumberCount = i2
End If
End Function[/tt]

It doesn't work if commas and spaces are intermixed: you should make a replace upon entering the function if you have such condition.
The function doesn't check whether the numbers are legitimate as such (in fact it counts the number of words); also I didn't check whether there are error conditions that could cause it to fail (it works for a blank cell though).


prex

http://www.xcalcs.com

Online tools for structural design

 

[joerd]

Now, without VBA you can substitute the spaces (or the commas) in the string, and then subtract the length of the new string from the length of the original string. For example, if you have in D5: [tt]12 23 34 54 3[/tt]
and in E5: [tt]=SUBSTITUTE(D5," ","")[/tt]
which results in [tt]122334543[/tt]
then [tt]=LEN(D5)-LEN(E5)+1[/tt] will give you the right answer.
You can of course substitute the formula in E5 into the last formula, and get the answer right away. You can also nest two substitute formulas, one that replaces the commas, one for the spaces.
Have fun!


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[gaijin2004]

Thank you for your response guys. Though it seems hard, I will try them one by one. I know I am on the right path.

 

[jghrist]

You can parse the cell contents as suggested by GregLock by using Data|Text to Columns.