counting instances of a character within a string

[Guest]

Hello,
I'm trying to count the number of times a character appears within a string.
I thought there was a function that could do this (i.e. one line of code),
but can't seem to find it.

For example, if I have a cell that contains "27,45,8,19,13" and want to
count the commas, I'm looking for something like this:

Num = CountChars("," , Cell.Value)
where CountChars is the mystery function that I'm seeking.

I can get the result I want with the Search function and a Do...Loop, but if
there was a function it would make it much cleaner.

Thanks.

Mike

 

[Chip Pearson]

Mike,

Try

Function CountChars(Txt As String, Char As String) As Integer
CountChars = Len(Txt) - Len(Replace(Txt, Char, ""))
End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...

 

[Guest]

Ahhhhhh....makes sense.

Thanks, Chip. I don't know if that would have ever occurred to me.

Thanks again.

Mike

 

[Guest]

Or you could use:
=LEN(D4)-LEN(SUBSTITUTE(D4,",",""))
I believe the first time I saw this used was in John Walkenbach's (sp?) book.

 

[Guest]

Never mind. Didn't read the post carefully enough (or remember which forum I
was in.)

 

[peter.thericgroup]

great work chip, it's alwaqys the simple functions that are the best!
very effective, thanksyou.