Results 1 to 7 of 7

Count letters in a string

This is a discussion on Count letters in a string within the Excel Questions forums, part of the Question Forums category; Hi all, Sorry if this has been covered, but I have not found it in my searches so far. How ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    82

    Default

    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?

    Thanks in advance.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,410

    Default

    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?

    Thanks in advance.
    =LEN(A1)-LEN(SUBSTITUTE(A1,E1,""))

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

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    82

    Default

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

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    810

    Default

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

    Aladin,
    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. #5
    Board Regular
    Join Date
    Jul 2002
    Posts
    82

    Default

    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. #6
    New Member
    Join Date
    Jan 2008
    Posts
    3

    Default 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. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,835

    Default Re: Count letters in a string

    Use UPPER or LOWER like this

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com