How to count the match letter within a string?
 

Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

With
A1: RRRR123RR

This formula returns the count of R's in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))

In this instance, B1 returns 6

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

On Tue, 21 Aug 2007 18:32:05 -0700, Eric
wrote:

Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which
is 5.
Does anyone have any suggestions?
Eric

=LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))

You can use a cell reference containing your match_letter in place of the "R"
in the above example.
--ron

How to count the match letter within a string?
 

Try this:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R",""))

Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1,
which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

Thank you very much for suggestions
Eric

"Ron Coderre" wrote:

With
A1: RRRR123RR

This formula returns the count of R's in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))

In this instance, B1 returns 6

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

I'm glad you could work with that......thanks for the feedback.



***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Thank you very much for suggestions
Eric

"Ron Coderre" wrote:

With
A1: RRRR123RR

This formula returns the count of R's in A1
B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))

In this instance, B1 returns 6

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Eric" wrote:

Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1, which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

To solve for its being case sensitive, use UPPER at both
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("r"),""))

"T. Valko" wrote:

Try this:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R",""))

Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to count the match letter within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1,
which
is 5.
Does anyone have any suggestions?
Eric

How to count the match letter within a string?
 

You could do that but if you know the letter needs to be in uppercase why
use UPPER("r") when you can just use "R"?

To make it foolproof it would be better to use a cell to hold the letter
then:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(A2),""))

Where A2 = R or r

--
Biff
Microsoft Excel MVP


"Tevuna" wrote in message
...
To solve for its being case sensitive, use UPPER at both
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("r"),""))

"T. Valko" wrote:

Try this:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R",""))

Note: SUBSTITUTE is case sensitive so the "R" needs to be in uppercase.

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"R","")) = 5
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"r","")) = 0

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to count the match letter
within
string?
such as "RRRRR" in cell A1, then return the number of "R" in cell B1,
which
is 5.
Does anyone have any suggestions?
Eric