





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
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:

 Copy above code.
 In Excel press Alt + F11 to enter the VBE.
 Press Ctrl + R to show the Project Explorer.
 Rightclick desired file on left (in bold).
 Choose Insert > Module.
 Paste code into the right pane.
 Press Alt + Q to close the VBE.
 Save workbook before any other changes.

Test the code:

 In a worksheet cell, enter the formula in the cell as =CountChar(Range,TextToFind)
 Range (required): A valid range consisting of one or more worksheet cells
 TextToFind (required): A string of 1 or more characters to be found in the specified range. (Any alphanumerical character is acceptable.)
 eg. =CountChar(A1:A10,"windows") will return the number of times the text string Windows is contained in the range A1:A10
 In VBA call the function in the same way as described above
 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.

