counting instances of a character within a string
 

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

counting instances of a character within a string
 

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



"mikelee101" wrote in
message
...
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

counting instances of a character within a string
 

Ahhhhhh....makes sense.

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

Thanks again.

Mike


"Chip Pearson" wrote:

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



"mikelee101" wrote in
message
...
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

counting instances of a character within a string
 

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.

"mikelee101" wrote:

Ahhhhhh....makes sense.

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

Thanks again.

Mike


"Chip Pearson" wrote:

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



"mikelee101" wrote in
message
...
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

counting instances of a character within a string
 

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

"Kleev" wrote:

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.

"mikelee101" wrote:

Ahhhhhh....makes sense.

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

Thanks again.

Mike


"Chip Pearson" wrote:

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



"mikelee101" wrote in
message
...
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

counting instances of a character within a string
 

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


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

"Kleev" wrote:

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.

"mikelee101" wrote:

Ahhhhhh....makes sense.

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

Thanks again.

Mike


"Chip Pearson" wrote:

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



"mikelee101" wrote in
message
...
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