counting charcters and digits but ignoring spaces and punctu
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: counting charcters and digits but ignoring spaces and punctu

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am attempting to count the number of characters and digits in a cell, but at the same time ignore punctuation marks and spaces.

    Example:
    1) See Pug run.
    charcter count would= 9
    2) Todays date is 04/17/2002.
    character count would =20

    I've read the posts about "counting problem" and "counting digits", but haven't been able to figure out how to modify those formulas to fit what I am looking for them to do. Can anyone give me any help?

    Thank You!
    Greg

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    {=SUM((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=
    {48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70
    ,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86
    ,87,88,89,90,97,98,99,100,101,102,103,104,105,106,107,108
    ,109,110,111,112,113,114,115,116,117,118,119,120,121,122}
    )+0)}

    ...where A1 contains your text string.

    Of course you could create a defined name, "Chars", that refers to...

    ={48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70
    ,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86
    ,87,88,89,90,97,98,99,100,101,102,103,104,105,106,107,108
    ,109,110,111,112,113,114,115,116,117,118,119,120,121,122}

    ...and then use the array formula...

    {=SUM((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=Chars)+0)}


    Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".

    [ This Message was edited by: Mark W. on 2002-04-17 15:59 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Also:


    =SUMPRODUCT((ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($48:$122),0)))+0)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 16:09, Aladin Akyurek wrote:
    Also:


    =SUMPRODUCT((ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($48:$122),0)))+0)
    But, this formula would count the following special characters...

    {":";";";"<";"=";">";"?";"@";"[";"";"]";"^";"_";"`"}

    ...producing a count of 12 instead of 11 for "Where is Jane?"

    [ This Message was edited by: Mark W. on 2002-04-17 16:23 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark:
    That's pretty neat! So, we picked up all the digits (ASCII 48 thru 57, all Uppercase 65 thru 90, and all Lowecase 97 thru 122).
    I need a bit of a lesson here. I believe Excel now supports Unicode, so when and where does Unicode come-in in contrast to ASCII/ANSI representation? Thanks for your help!

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-17 16:20, Mark W. wrote:
    On 2002-04-17 16:09, Aladin Akyurek wrote:
    Also:


    =SUMPRODUCT((ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($48:$122),0)))+0)


    But, this formula would count the following special characters...

    {":";";";"<";"=";">";"?";"@";"[";"";"]";"^";"_";"`"}

    ...producing a count of 12 instead of 11 for "Where is Jane?"

    [ This Message was edited by: Mark W. on 2002-04-17 16:23 ]
    Back to school!

    [ This Message was edited by: Aladin Akyurek on 2002-04-17 16:47 ]

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It will be slower than the Mark's worksheet formula, but how about this UDF

    ---------------
    Function NumChars(x)
    Dim length As Integer, temp As String
    Dim y As Integer, check As Byte

    For y = 1 To Len(x)
    check = Asc(Mid(x, y, 1))
    If check >= 48 And check <= 57 Then temp = temp & Mid(x, y, 1)
    If check >= 65 And check <= 90 Then temp = temp & Mid(x, y, 1)
    If check >= 97 And check <= 122 Then temp = temp & Mid(x, y, 1)

    Next y
    NumChars = Len(temp)

    End Function
    --------------------

    Bye,
    Jay

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay,

    Gave your formula a quick run through and looks like it worked perfectly. Thanks much!!

    Best Regards,

    Still hvaen't solved the counting in bases question. If u got any suggestions they would be appreciated!


  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 21:27, youbet7469 wrote:
    Hi Jay,

    Gave your formula a quick run through and looks like it worked perfectly. Thanks much!!

    Best Regards,

    Still hvaen't solved the counting in bases question. If u got any suggestions they would be appreciated!

    Hi,

    I did respond to your follow up question. See the other thread. Not sure the reason behind it, though. Whatever you do, don't mess with Damon's formula. It is working perfectly.

    Bye,
    Jay

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 16:42, Yogi Anand wrote:
    Hi Mark:
    That's pretty neat! So, we picked up all the digits (ASCII 48 thru 57, all Uppercase 65 thru 90, and all Lowecase 97 thru 122).
    I need a bit of a lesson here. I believe Excel now supports Unicode, so when and where does Unicode come-in in contrast to ASCII/ANSI representation? Thanks for your help!
    Yogi, unfortunately Excel's CODE worksheet function only supports Windows' ANSI character set. So even though you can enter (paste) the Greek letter Delta (), the CODE worksheet function will "see" it as ASCII 63 (?).

User Tag List

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