How to count the match letter within a string?

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

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
 
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
 
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
Click to expand...

=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
 
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
 
Thank you very much for suggestions
Eric

Ron Coderre said:
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
Click to expand...
 
I'm glad you could work with that......thanks for the feedback.



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

XL2003, WinXP
 
To solve for its being case sensitive, use UPPER at both
=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER("r"),""))
 
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
 
Back
Top