Counting the number of items in a cell
Counting the number of items in a cell
(OP)
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!





RE: Counting the number of items in a cell
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
RE: Counting the number of items in a cell
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: www.essexsteam.co.uk
RE: Counting the number of items in a cell
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
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
RE: Counting the number of items in a cell
and in E5: =SUBSTITUTE(D5," ","")
which results in 122334543
then =LEN(D5)-LEN(E5)+1 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.
RE: Counting the number of items in a cell
RE: Counting the number of items in a cell