counting entries within a cell's formula

[Mark]

I am interested in doing a count of entries within a cell. It would be
similar to a word count, but instead of counting words, I need to count the
plus signs (+) or something like that. I have a simple formula in the cell
like:

=100+1235+3456+0987+etc

and am only interested in determining how many entries are in there since
each number entry represents a program and I would like to know how many
programs are accounted for.

I've tried this variation on the word wount, but no success:

=IF(LEN(TRIM(B4))=0,0,LEN(TRIM(B4))-LEN(SUBSTITUTE(B4,CHAR(43)," "))+1)

 

[bj]

your equation is ok except chnge " " to ""
a space counts as one character.

 

[Joel]

thsi custom function will count plus signs. The number of words is one more
than the plus signs

Function CountPlus(Mycell As Range)

MyString = Mycell.Formula
CountPlus = 0
For i = 1 To Len(MyString)

If StrComp("+", Mid(MyString, i, 1)) = 0 Then
CountPlus = CountPlus + 1
End If

Next i


End Function

 

[Mark]

I've done my share of heavy Excel programming, but am a bit lost as to how
you would write this into a functional formula. Can you please elaborate on
how to create this custom function? Thanks

 

[Joel]

I don't know if my previous reply got posted.
It is easy to use this function.

1) go to the tab on the bottom of the worksheet (normally sheet1) and right
click. Select view code
2) Go to VBA Menu and select insert Module. a new module 1 will be shown.

3) Paste code from Function to End Function.
4) Remove the > that this website inserts.
5) You are done.


Use this function like any other function

=CountPlus(A3)

A3 will have a formula like =A1+A2

 

[Mark]

cool, it works great, thanks for the help! And now I understand how to ad
some new custom functionality too!

 

[Joel]

I grind my teeth everytime I see people write complicated functions on the
worksheet when they can write code that can easily be debug in VBA.

to test the code there is a full debugger in VBA. Highlight the 1st line of
the function and press F9. The line goes yellow indicating this is a break
point. Now go back to the worksheet and change the cell calling the
function. Excel will go to VBA and stop on the code. You can see the values
of variables by moving the mouse over the variables. You can also add the
variables to a watch window by right clicking on the variable. Very Powerful.

 

[Gord Dibben]

Joel

May I modify the CountPlus Function a bit to provide a little more flexibility?

Function CountPlus(Mycell As Range, letter As String)
MyString = Mycell.Formula
CountPlus = 0
For i = 1 To Len(MyString)
If StrComp(UCase(letter), Mid(MyString, i, 1)) = 0 Then
CountPlus = CountPlus + 1
End If
Next i
End Function

Usage is: =CountPlus(cellref,"char")

Where "char" can be any character rather than just the "+" sign.


Gord Dibben MS Excel MVP

 

[Joel]

Then you should also change the name of the function! It would silly to have
a function called CountPlus() counting minus signs.