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:

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:

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

Approved by mdmackillop

This entry has been viewed 170 times.

Copyright @2004 - 2014 VBA Express