How to count entries in a cell...?

Posted by kertappa on July 01, 2001 8:17 AM

Does anyone know a way of counting the numbers in a single cell? It's hard to explain but here goes....

Cell contains "+10+5+8+7+45" and shows the total of 75.
But I want another cell to count the numbers, which is 5.

Or another cell containing "+500+20" shows 520, but another cell would display 2....

Thanks in advance....

Posted by Cory on July 01, 2001 11:09 AM

Count

Try this forula in the cell you want the result in:

Count(a1:a5)

In this formula a1:a5 would contain the numbers you gave in the example

Posted by Aladin Akyurek on July 01, 2001 11:45 AM

Re: Count

Cory,

I believe what Kertappa is asking is something diffrent. Something that cannot be done, as far as I know, with ordinary formulas.

Aladin

Posted by Dax on July 01, 2001 1:47 PM

Hello,

I think that you can only do this using VBA. You can use this custom function which will count the individual items (numbers or cell references) in between the four operator +,-,* and /. To use this code open the VB Editor (Alt+F11) and click Insert, Module. Then paste this code: -

Public Function CountItems(rnge As Range) As Long
Dim sFormulaString As String, x As Long, sOper As String
If rnge.Rows.Count > 1 Or rnge.Columns.Count > 1 Then Exit Function
sFormulaString = rnge.Formula
If sFormulaString = "" Then Exit Function
CountItems = 1
For x = 2 To Len(sFormulaString)
sOper = Mid$(sFormulaString, x, 1)
Select Case sOper
Case "+", "*", "-", "/"
CountItems = CountItems + 1
End Select
Next x
End Function

Now if you go into an empty cell and type =CountItem(A5) or whatever cell you want to count the items in you should get what you want.

HTH,
Dax.

Posted by kertappa on July 01, 2001 2:12 PM

Thanks Dax!

And everybody else who took the time to reply....

You just made my day!

Matthew
www.kertappa.co.uk