counting charcters and digits but ignoring spaces and punctu

youbet7469

New Member
Joined
Mar 20, 2002
Messages
12
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
{=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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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! :cool:
This message was edited by Aladin Akyurek on 2002-04-17 16:47
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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 (?).
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top