1. ## Solved: Character Count

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!   Reply With Quote

2. Hello Andrew, to count the occurances of a char in a string you might think about using next formula:
=LEN(A1) - LEN(SUBSTITUTE(Range, "YOURCHAR", "")) like
=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:
[VBA]
Function COUNTCH(rng As Object, strText As String )
Application.Volatile
COUNTCH = Len(rng) - Len(Application.Substitute(rng, strText, ""))
End Function
[/VBA]
Place this function into a module and called it like: =COUNTCH(A1,"e")

Regards,
Jeroen  Reply With Quote

3. If you want to count the number of occurances within multiple cells you might think about using a function like:
[VBA]
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
[/VBA]

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

4. Substitute is case sensitive. You need to change the formula given to:
=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.  Reply With Quote

5. 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:
[VBA]
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
[/VBA]

to use this:
case sensitive: =CharCount2(A1:A10, "e", TRUE)
non-casesensitive: =CharCount2(A1:A10, "e")  Reply With Quote

6. Good point Cesar,

How about a slight modification?[vba]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[/vba]

EDIT : I see Jeroen had the same idea EDIT2 : Extended to cover more than one cell  Reply With Quote

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

8. 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  Reply With Quote

9. 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.  Reply With Quote

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

[VBA] :
:
Next rngCell
lCounter = lCounter / Len(strText)
COUNTCH = lCounter

End Function
[/VBA]  Reply With Quote

11. ## Hello Andrew,?@ Hi guys, very nice approach, I add Extended to cover more than one RANGE with using ParamArray.
Something like this..

=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?

[vba]
Option Explicit
Sub Auto_Open()
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
[/vba]

HTH  Reply With Quote

12. ## Thanks Guys!

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   Reply With Quote

13. 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  Reply With Quote

14. 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
=CountChar(Range,"character")
=CountChar(A1:A5,"e")
[VBA]
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
[/VBA]

edit:Thanks Dreamboat, It looks great this way.  Reply With Quote

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

16. ## More code for me to study!

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   Reply With Quote

17. My code will work with multiple or single range values. Doesn't matter.  Reply With Quote

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

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

19. Who's going to volunteer to make this a KB entry? I nominate Andrew.  Reply With Quote

20. 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 )  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•