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