+ Reply to Thread
Results 1 to 11 of 11

Counting Unique Characters Within a Single Cell

  1. 04-14-2015, 09:12 PM #1
    camsbigblue
    camsbigblue is offline
    Registered User
    Join Date
    04-14-2015
    Location
    Penn State
    MS-Off Ver
    Excel 2013
    Posts
    2

    Counting Unique Characters Within a Single Cell

    Hey guys,

    I'm working on an assignment for my excel class, and I'm having trouble for the first time that I can't find a solution for online.

    So, there is one cell that has a quote in it that changes every time excel refreshes (pressing F9 or entering a new formula.)

    For the question I am stuck on, I have to use functions to count the number of different letters within the cell, say which letter appears most, then state how many times it appears, each of these three being in a separate cell.

    Any clue on how to go about this?
    Reply With QuoteRegister To Reply
  2. 04-14-2015, 09:22 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Unique Characters Within a Single Cell

    This will count the unique characters in a cell.

    Array entered**:

    =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:255"))),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Note that this is NOT case sensitive. "A" and "a" are equal.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 04-14-2015, 09:25 PM #3
    Shannon561
    Shannon561 is offline
    Registered User
    Join Date
    02-25-2014
    Location
    Brisbane
    MS-Off Ver
    Excel 2000
    Posts
    78

    Re: Counting Unique Characters Within a Single Cell

    To count how many characters appear in a cell use

    =LEN(A1)
    Reply With QuoteRegister To Reply
  4. 04-14-2015, 09:33 PM #4
    camsbigblue
    camsbigblue is offline
    Registered User
    Join Date
    04-14-2015
    Location
    Penn State
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Counting Unique Characters Within a Single Cell

    That worked perfect, thank you so much! I'd been stuck on this for the longest time
    Reply With QuoteRegister To Reply
  5. 04-14-2015, 09:49 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Unique Characters Within a Single Cell

    I forgot about the other questions.

    For the character that appears the most frequently...

    Array entered**:

    =CHAR(MODE(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    For the number of times the most frequent character appears...

    Assume the above formula is in cell C1:

    =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),C1,""))
    Reply With QuoteRegister To Reply
  6. 04-14-2015, 11:03 PM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Counting Unique Characters Within a Single Cell

    Quote Originally Posted by Tony Valko View Post
    This will count the unique characters in a cell.

    Array entered**:

    =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:255"))),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Note that this is NOT case sensitive. "A" and "a" are equal.
    Hi Tony,

    The formula seems to be off by one because it counts one space character.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 04-15-2015, 01:31 AM #7
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,165

    Re: Counting Unique Characters Within a Single Cell

    Hi,

    I find an easy formula for the number of different letters in a past post:
    http://www.excelforum.com/excel-form...-a-string.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  8. 04-15-2015, 01:36 AM #8
    Ali Kırksekiz
    Ali Kırksekiz is offline
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Counting Unique Characters Within a Single Cell

    In the present case,

    You can put SUBSTITUTE(A1," ","") instead of A1

    Tony's formula

    Please Login or Register  to view this content.
    CTRL,SHIFT,ENTER (not just ENTER)
    Reply With QuoteRegister To Reply
  9. 04-15-2015, 03:48 AM #9
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Counting Unique Characters Within a Single Cell

    Quote Originally Posted by camsbigblue View Post
    ..... I have to use functions to count the number of different letters within the cell
    For different letters only, assuming "A" = "a" then try this formula

    =SUMPRODUCT(ISNUMBER(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1))+0)

    or, shorter but requiring "array entry"

    =COUNT(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1))
    Last edited by daddylonglegs; 04-15-2015 at 03:52 AM.
    Audere est facere
    Reply With QuoteRegister To Reply
  10. 04-15-2015, 08:54 AM #10
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Unique Characters Within a Single Cell

    Quote Originally Posted by AlKey View Post
    Hi Tony,

    The formula seems to be off by one because it counts one space character.
    Space characters are counted. The OP didn't mention if they were present or if they need to be excluded from the count.
    Reply With QuoteRegister To Reply
  11. 04-15-2015, 07:26 PM #11
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counting Unique Characters Within a Single Cell

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting Cell Characters
    By TBeverson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2009, 03:30 PM
  2. Counting the Occurence of Special characters in Single/Multiple cells?
    By all4excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-23-2008, 03:33 PM
  3. [SOLVED] counting characters in a cell
    By ram in forum Excel General
    Replies: 3
    Last Post: 07-29-2006, 12:10 PM
  4. count of tab characters in a single cell
    By mark in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2006, 02:00 PM
  5. VBA - Counting Characters in Cell
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2005, 06:22 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1