Count occurances of a pattern of characters (numbers or text) in a range

Ease of Use


Version tested with

97, 2003 

Submitted by:

Ken Puls


Counts the number of times a user specified string of numbers or text exists in a range of cells 


This function was originally written to find the last space in a text string, but has been expanded to also deal with numbers. It can operate on a single cell or a range of cells, and can look for a single character or a string of characters (either text or numeric). This formula is probably most useful to return results for further use in other formulas. CAVEAT: This formula will NOT accept a formula result as an arguement (ie countchar(left(A1,15),"o") will NOT work) 


instructions for use


Option Compare Text Option Explicit Function CountChar(Rng As Range, TextToFind As String) As Double 'Function purpose: To count number of instances of a string of 'characters in a range Dim cl As Range, x As Integer For Each cl In Rng For x = 1 To Len(cl) If Mid(cl, x, Len(TextToFind)) = TextToFind Then CountChar = CountChar + 1 End If Next x Next cl End Function

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. In a worksheet cell, enter the formula in the cell as =CountChar(Range,TextToFind)
  2. Range (required): A valid range consisting of one or more worksheet cells
  3. TextToFind (required): A string of 1 or more characters to be found in the specified range. (Any alphanumerical character is acceptable.)
  4. eg. =CountChar(A1:A10,"windows") will return the number of times the text string Windows is contained in the range A1:A10
  5. In VBA call the function in the same way as described above
  6. eg. msgbox CountChar(activesheet.range(A1:A10),"windows") will return the same information as above.

Sample File:

CountChar function demo.zip 8.64KB 

Approved by mdmackillop

This entry has been viewed 170 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express