counting charcters and digits but ignoring spaces and punctu

[youbet7469]

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

[Mark W.]

{=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 ]

[Aladin Akyurek]

Also:


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

[Mark W.]

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 ]

[Yogi Anand]

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!

[Aladin Akyurek]

On 2002-04-17 16:20, Mark W. wrote:

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 ]

[Jay Petrulis]

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

[youbet7469]

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!

[Jay Petrulis]

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

[Mark W.]

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 (?).