Results 1 to 4 of 4

Thread: Count words having more than one character in a cell

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb Count words having more than one character in a cell

    Hi All,

    Here is a formula based solution to count words having more than one character in a cell.

    If you have data in A2:Ax on Sheet1,

    select B2 (It's very important)

    Hit CTRL + F3 (to open the Name Manager)

    Click on New ;

    Name: String

    Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2," ",""";""")&"""}")

    Now in B2 and copied down,

    =INDEX(FREQUENCY(LEN(String),{1}),2)

    Hope this helps !
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Here is a formula based solution to count words having more than one character in a cell.

    If you have data in A2:Ax on Sheet1,

    select B2 (It's very important)

    Hit CTRL + F3 (to open the Name Manager)

    Click on New ;

    Name: String

    Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2," ",""";""")&"""}")

    Now in B2 and copied down,

    =INDEX(FREQUENCY(LEN(String),{1}),2)
    I am pretty sure this simple, direct formula placed in B2 and copied down will do the same thing as what you have posted does...

    =1*(LEN(A2)>1)

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Rick,

    I think you mis-interpreted the sentence. My formula would give you the count of those words only which have more than one character in a cell.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Rick,

    I think you mis-interpreted the sentence. My formula would give you the count of those words only which have more than one character in a cell.
    Yes, you are right... I did mis-interpret your sentence. Sorry for any confusion that may have caused. However, with that said, there is still a direct (although not all that simple) formula available to obtain this count...

    =SUMPRODUCT(1*(LEN(TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",999)),ROW(INDIRECT("A1:A999"))*999-998,999)))>1))

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  2. Macro To Delete Numbers With Trailing Character
    By Howardc in forum Excel Help
    Replies: 8
    Last Post: 04-05-2013, 08:14 PM
  3. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  4. Replies: 6
    Last Post: 09-26-2011, 07:39 AM
  5. Formula To Identify A Cell Has Duplicate Words
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-03-2011, 05:52 AM

Tags for this Thread

Posting Permissions

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