Character Count

**Andrew** · 06-20-2004, 08:44 AM

Hello the Forum,

I have a little project that I am working on, which will require counting characters inside a cell.

Let's say in cell A1, I have entered "VBA Express Forum" and I want to know how many times the character "e" occurs within the string, the answer should be 2. (Case does not matter, spaces and slashes should be included so " " or "/" will also be counted as well)

As there does not seem an exisiting formula to do so already(?), I am wondering if it possible to make a Private Sub in VBA that will do the trick. I am thinking of something like =COUNTCH(A1,"e") to return 2.

Any help you can give me would be appreciated. Thanks!

**roos01** · 06-20-2004, 09:20 AM

Hello Andrew, to count the occurances of a char in a string you might think about using next formula:

PHP Code:


=LEN(A1) - LEN(SUBSTITUTE(Range, "YOURCHAR", ""))

like

PHP Code:


=LEN(A1) - LEN(SUBSTITUTE(A1, "e", ""))

where A1 contains the string and the char you wanted to look for is "e".

if you want to use a User Defined function for this you might think about a function like:

Function COUNTCH(rng As Object, strText As String )
Application.Volatile
COUNTCH = Len(rng) - Len(Application.Substitute(rng, strText, ""))
End Function

Place this function into a module and called it like: =COUNTCH(A1,"e")

Regards,
Jeroen

**roos01** · 06-20-2004, 09:42 AM

If you want to count the number of occurances within multiple cells you might think about using a function like:

Function CharCount(rng As Object, strText As String)
Dim Counter As Integer
Application.Volatile
For Each rngVal In rng
    Counter = Counter + Len(rngVal) - Len(Application.Substitute(rngVal, strText, ""))
Next rngVal
CharCount = Counter / Len(strText)
End Function

to use this: =CharCount(A1:A10,"e")

**xXLdev** · 06-20-2004, 10:06 AM

Substitute is case sensitive. You need to change the formula given to:

PHP Code:


=LEN(A1) - LEN(SUBSTITUTE(LOWER(A1), LOWER("e"), ""))

I for some reason keep getting an error when I try changing the given UDFs. I am short on time, so I will post this now.

**roos01** · 06-20-2004, 10:23 AM

Like that thinking about using the case sensitive option. here another function which allows the user to count the chars within a range and detemine if he/she wants to count using casesensitive or not:

Function CharCount2(rng As Object, strText As String, Optional CaseSence As Boolean)
Dim Counter As Integer
Application.Volatile
If CaseSence = True Then
    For Each rngVal In rng
        Counter = Counter + Len(rngVal) - Len(Application.Substitute(rngVal, strText, ""))
    Next rngVal
Else
For Each rngVal In rng
    Counter = Counter + Len(rngVal) - Len(Application.Substitute(LCase(rngVal), LCase(strText), ""))
Next rngVal
End If
CharCount2 = Counter / Len(strText)
End Function

to use this:
case sensitive:

PHP Code:


=CharCount2(A1:A10, "e", TRUE)

non-casesensitive:

PHP Code:


 =CharCount2(A1:A10, "e")

**Richie(UK)** · 06-20-2004, 10:33 AM

Good point Cesar,

How about a slight modification?

Function COUNTCH(rng As Object, strText As String, _
Optional bCaseSen As Boolean = False) As Long
Dim rngCell As Range, lCounter As Long
lCounter = 0
For Each rngCell In rng
    If bCaseSen = True Then
        lCounter = lCounter + Len(rngCell.Value) - _
        Len(Application.Substitute(rngCell.Value, strText, ""))
    Else
        lCounter = lCounter + Len(rngCell.Value) - _
        Len(Application.Substitute(LCase(rngCell.Value), LCase(strText), ""))
    End If
    Next rngCell
    COUNTCH = lCounter
End Function

EDIT : I see Jeroen had the same idea

EDIT2 : Extended to cover more than one cell

**roos01** · 06-20-2004, 10:38 AM

LOL, same idea but now at least were complete. one function for a range and one function to be used for just one cell.

**brettdj** · 06-20-2004, 05:40 PM

Nice stuff guys......

BTW Jeroen, you don't need the
Application.Volatile
as the Function will update if any of the input parameters change

Cheers

Dave

**xXLdev** · 06-20-2004, 09:44 PM

The function needs to catch the case where the strText is not a single character. Either return an error or modified to handle strings of more than 1 character.

If you call countch(A1,"his") with A1= "This is a test" it returns 3. Definitely not the right answer. Either 1 or #value should be returned.

I am not sure what Andrew wanted. My opinion would be #value.

**TonyJollans** · 06-21-2004, 12:25 AM

To generalise for any length string just factor in the length of the string. Amend the end of Richie's code like this ..

Next rngCell 
    lCounter = lCounter / Len(strText)
    COUNTCH = lCounter 
End Function

**Colo** · 06-21-2004, 12:40 AM

Hello Andrew,?@

Hi guys, very nice approach, I add Extended to cover more than one RANGE with using ParamArray.
Something like this..

PHP Code:


=COUNTCH("a",FALSE,B1:B3,C4:C7,E1:E10)

As the HELP file says, ParamArray cannot be used with Optional keyword...so it depends on which one does Andrew want.

Regarding to the problem that Cesar wrote, why don't you divide the result by the count of text?

Option Explicit
Sub Auto_Open()
'Add discription to this UDF
Application.MacroOptions Macro:="COUNTCH", _
Description:="This is an UDF that counting characters inside a cell ", _
HasShortcutKey:=False, _
Category:=7
End Sub

Function COUNTCH(strText As String, _
bCaseSen As Boolean, _
rng As Range, _
ParamArray OtherRng()) As Long
Dim rngCell As Range, lCounter As Long, i As Long
lCounter = 0
For Each rngCell In rng
    lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
Next rngCell
For i = 0 To UBound(OtherRng)
    For Each rngCell In OtherRng(i)
        lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
    Next
Next
COUNTCH = lCounter
End Function

Private Function CountUp(r As Range, _
lCounter As Long, _
sTxt As String, _
bCaseSen As Boolean) As Long
If bCaseSen Then
    lCounter = (Len(r.Value) - _
    Len(Application.Substitute(r.Value, sTxt, ""))) / Len(sTxt)
Else
    lCounter = (Len(r.Value) - _
    Len(Application.Substitute(LCase(r.Value), LCase(sTxt), ""))) / Len(sTxt)
End If
CountUp = lCounter
End Function

HTH

**Andrew** · 06-21-2004, 09:18 AM

Wow, talk about lots of help at hand! Thanks a lot, I really appreciate it!

As a VBA newbie, it will take me a while to go through all the code, but I can see how the text functions work easily enough.

Substitute the characters to be counted with spaces, then subtract the length (number) of the spaces from the length of the string. Then I see the suggestion of using lower case or (upper?) to ensure all characters are recognized by the SUBSTITUTE function.

Perfect! This does exactly what I am after although I think I will work with an Imput Box later on to enter a string so I'll definitely be looking at the VBA code in order to make this possible.

Without trying to embarass myself too much, could someone please explain why the Object variable is used and what Application.Volatile means.

Thanks again, you guys are the greatest

**roos01** · 06-21-2004, 09:51 AM

Hi Dave,
I put in the example application.volatile so when ever the calculation is set to manual it still calculates the number of chars.

Andrew:
from VBA help:
Object variables are stored as 32-bit (4-byte) addresses that refer to objects. Using the Set statement, a variable declared as an Object can have any object reference assigned to it.

Volatile Method
Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell

**CBrine** · 06-22-2004, 07:49 AM

Here's a function for counting characters, using the character and range object. Makes the code a bit smaller. It will work with a single character on multiple ranges, ignoring case, doesn't care what the character is. Will return an error if you use more then 1 character in the selection

syntax

PHP Code:


=CountChar(Range,"character")
=CountChar(A1:A5,"e")

Function CountChar(R As Range, Char As String)
Dim c As Characters, Found As Integer, count1 As Integer, R2 As Range
If Len(Char) <> 1 Then
    CountChar = "#N/A"
    GoTo Error
End If
For Each R2 In R
    For count = 1 To R2.Characters.count
        If UCase(R2.Characters(count, 1).Text) = UCase(Char) Then
            Found = Found + 1
        End If
    Next count
Next
CountChar = Found
Error:
End Function

edit:Thanks Dreamboat, It looks great this way.

**Anne Troy** · 06-22-2004, 08:01 AM

Hi, CBrine! Welcome!!
Check out the link in my signature, and then edit your post to use VBA tags. They're VERY cool!

**Andrew** · 06-22-2004, 09:02 AM

Hello again,

I'm working my way down these posts one at a time absorbing wherever possible. Even if I don't understand everything it certainly helps me to go through the code and see what I can pick up.

Just for the record, I was just thinking of counting single characters in a single cell (string).

You've all been a tremendous help

**CBrine** · 06-22-2004, 10:07 AM

My code will work with multiple or single range values. Doesn't matter.

**NateO** · 06-22-2004, 11:20 AM

Another multi-range, [non vba] approach includes:

=SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,{"e","E"},"")))

**Andrew** · 06-23-2004, 06:37 PM

Dreamboat has nominated me to make this thread a KB entry.

I'm okay with the non-VBA formulas but knowing which VBA I should use for the entry is causing smoke to come out from my ears.

How about it guys, help a newbie out? (No kicking or scratching please

)

Thread: Character Count

Thread Tools

Display

Character Count

Thanks Guys!

More code for me to study!

Posting Permissions