counting instances of a character within a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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]...
 
Ahhhhhh....makes sense.

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

Thanks again.

Mike
 
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.
 
great work chip, it's alwaqys the simple functions that are the best!
very effective, thanksyou.
 
Back
Top