Count letters in a string

Thanks:  0
Likes:  0

# Thread: Count letters in a string

1.
Hi all,
Sorry if this has been covered, but I have not found it in my searches so far.
How can I determine the number of times a specific letter appears in a given string?

2. On 2002-07-04 07:33, pamaga wrote:
Hi all,
Sorry if this has been covered, but I have not found it in my searches so far.
How can I determine the number of times a specific letter appears in a given string?

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

where A1 houses a string and E1 the target letter to count.

3. Many thanks.
Now, why didn't I thnk of that?...
Thanks again

4. Are you trying to do this with a cell formula, or with VBA code?

Just saw your formula, very clever! I like it.

Steve

P.S. The formula is case-sensitive, but that can be modified, if desired.

[ This Message was edited by: stevebausch on 2002-07-04 08:06 ]

5. Very clever indeed, hey?...
In my version of Excel I had to change things a bit (also to accommodate upper- and lowercase variants), but once the concept was shown, everything is easy.
Conceptually simple, elegant, and very smart... And enough praise now!

6. ## Re: Count letters in a string

How can I adjust this formula to count A or a as the same letter.

Example And Atom was = 3 A's

7. ## Re: Count letters in a string

Use UPPER or LOWER like this

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•