How to count the match letter within a string?

's Computer Specifications

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

's Computer Specifications

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

's Computer Specifications

On Tue, 21 Aug 2007 18:32:05 -0700, Eric <(E-Mail Removed)>
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

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:C27F7DB2-E79E-4A86-8960-(E-Mail Removed)...
> 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

's Computer Specifications

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

's Computer Specifications

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

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
> news:C27F7DB2-E79E-4A86-8960-(E-Mail Removed)...
> > 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
>
>
>

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:F3064E1D-CB5E-4A4C-9301-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:C27F7DB2-E79E-4A86-8960-(E-Mail Removed)...
>> > 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
>>
>>
>>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
count no of times a letter occurs in a text string	nishad	Microsoft Excel Misc	4	10th Jan 2008 03:13 PM
Count letter string, e.g. h/EL/p	=?Utf-8?B?TGVl?=	Microsoft Excel Worksheet Functions	16	4th Sep 2007 11:57 AM
How can I count the number of times a letter repeats in a string?	=?Utf-8?B?V2lsZXk=?=	Microsoft Excel Worksheet Functions	3	11th May 2006 06:53 PM
Re: Converting a string to a string that contains the ASCII values of each letter in the origional string	Mikael Jansson	Microsoft C# .NET	0	31st Jul 2003 08:42 PM
Re: Converting a string to a string that contains the ASCII values of each letter in the origional string	Jon Skeet	Microsoft C# .NET	0	31st Jul 2003 08:38 PM