VBA Code

Special thanks to Jim Byrne who modified my code for counting commas and provided this.

In your worksheet select (from the top menu) Tools|Macro|Visual Basic Editor. On the left, you will see a window titled "Project". Select your spreadsheet (for example, my spreadsheet is called Book 1, look for VBAProject (Book 1)). Then, from the top menu, select Insert|Module. On the right window you will have a blank window representing the new module you just created. Now paste the following code:


Function Count_String(Range, Text As String) As Integer
' Written by Barrie Davidson
Dim Length As Variant
Dim Counter As Integer
Dim Position As Integer
Dim Text_to_look_for As String
Dim Text_to_compare As String
     
     Text_to_look_for = Text
     Length = Range.Value
     Length = Len(Length)
     Counter = 0
     Position = 1
     Do Until Position > Length
         Text_to_compare = Mid(Range.Value, Position, Len(Text_to_look_for))
         If Text_to_compare = Text_to_look_for Then
             Count_String = Count_String + 1
         Else
         End If
         Position = Position + 1
     Loop
End Function

After you have pasted the code, you can close the visual basic editor window (this doesn't take you out of Excel). To access this custom function, (from the top menu in Excel), select Insert|Function. The Paste Function menu box will pop up. Select User Defined in the left window and then select the function you created from the right window.