Character Count

[Andrew]

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]

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:



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]

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]

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.

[roos01]

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:
=CharCount2(A1:A10, "e", TRUE)
non-casesensitive:
=CharCount2(A1:A10, "e")

[Richie(UK)]

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]

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]

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]

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]

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]

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?



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]

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]

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 (javascript:hhobj_4.Click()) 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]

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")




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]

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

[Andrew]

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]

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

[NateO]

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


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

[Anne Troy]

Who's going to volunteer to make this a KB entry? :)

I nominate Andrew.

[Andrew]

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 ;) )

[NateO]

Andrew,

To be honest, based on your original question, in my estimation, VBA need not apply here. While I love VBA, there's a few different aspects to maximizing Excel's functionality.

If you had an array of strings in Visual Basic I might say otherwise, but that's a different matter. I would not have posted otherwise. :)

[Anne Troy]

The idea is not to *waste* the code.
Nate: could you provide a scenario/sample file where this code might be nice? Something we can add to the kb?

[Andrew]

Thanks Nate and Dreamboat.

Seeing that non-VBA formulas could count characters in both single cells and multiple cells, I agree that VBA isn't really required, but at the same time I think it's a good reference for people like me to see how it can be done. (I'm still kicking myself to see how easily Jeroen did it with LEN and SUBSTITUTE, plus the case sensitive fix that Cesar added. The SUMPRODUCT formula is really good too)

I think that just familiarizing myelf with the VBA variables etc does a lot of good overall. Gets me thinking how I can use to them to write my own code and get beyond the 'fumbling' stage :)

[CBrine]

Mine.:p

Just kidding. Andrew has a valid point, we have about 3 different solutions posted on this one topic. They all most likely work(I can only vouch for mine). Who makes the decision on which code gets put into the KB? Andrew was the one looking for the solution, so would not be the one to evaluate it. If he had the skills to do that, he wouldn't have needed us. My suggetion would be a peer review(Vote!!!!)

[Andrew]

I certainly don't want to step on any toes here, any code that works is good code to me (though I suppose that shorter code has certain advantages)

I'd like nothing more to contribute in any way I can :)

[Anne Troy]

Ok. If someone can *name* the 3 solutions, let's do a poll. :)

On a side note...
We don't have to know how to write the code to create a kb entry. We only have to know how to use it. I certainly did not write the ones that are in there under my name. If the person I *stole* the code from is a member, then I put the code in under that person's name (but only admins can do that), so I've really made many more kb entries than it appears.

[CBrine]

Dreamboat,

My point was not to the fact that he could create the entry or use the code. It was to the fact is he the best person to evaluate the best solution out of the multiple ones presented. Or again, does this even matter, as long as the first solution does the job described?

[Anne Troy]

This one's become a moot point, since Nate informs me that there wouldn't ever really be a reason for someone to use code for this purpose, or that it would be very rare for someone to do so.

As for the kb entries...well, there's ALWAYS room for improvement, right?

I figure it this way: If you've got what you believe to be better code than what already exists in the kb, then you let the person who created the article know about it. If they agree, cool. If not, create your own kb entry using (hopefully) the same title, with something a little different at the end that describes the method. While this is NOT a good example literally:

Copy Rows to a New Worksheet - Looping Method
Copy Rows to a New Worksheet - Whatever Method

I don't think we need to be so rigid about our kb entries. While I'd love to enforce good coding standards, we'll never get anything in there at this rate. The other question on autonumbering of Excel files has turned into a circus. I don't blame anyone...we're all trying to do our best. But I don't know that there's a perfect way, and I don't feel we need to provide a perfect way. I think the simpler way is better. We will help no one if we continue to dissect every single piece of code that gets put up there. I also think that few are creating entries for fear someone will question it. I have said before that if I must, I will create all the kb entries myself. I will have this kb if it kills me, and it just might, LOL! Anyway, everyone seems to be making too big of a deal out of this. Without perfect coders, I cannot enforce perfect coding. And perfect coders are too busy to be hanging around here, I think. I hope this is a place for learning. Some of the best coders I know appear to be learning...

[Anne Troy]

Marked solved. :)