How to count the match letter within a string?

[Guest]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[T. Valko]

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

 

[Guest]

Thank you very much for suggestions
Eric

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

 

[Guest]

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



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

XL2003, WinXP

 

[Guest]

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

 

[T. Valko]

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