Excel

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

Ease of Use

Easy

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

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

Discussion:

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) 

Code:

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 172 times.

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