How can I count the number of caracters including spaces in a microsoft excel
worksheet

Try...

=LEN(A2)

Hope this helps!

In article ,
Jan wrote:

How can I count the number of caracters including spaces in a microsoft excel
worksheet

Subject says cell, body says worksheet. I'll tackle the cell issue:

for a cell you can use the formula
= LEN(A1)
where A1 is the address of the cell with the text you need to find the
number of characters for.

"Jan" wrote:

How can I count the number of caracters including spaces in a microsoft excel
worksheet

There is a feature of the "BuiltIndocumentProperties" Property in VBA that
will supposedly return the "number of characters (with spaces)", but it does
not respond in my XL97 installation.

hth
Vaya con Dios,
Chuck, CABGx3



"Jan" wrote:

How can I count the number of caracters including spaces in a microsoft excel
worksheet

Jan wrote:
How can I count the number of caracters including spaces in a microsoft excel
worksheet

On a worksheet?

Try:

=SUM(LEN(Sheet1!1:32768))+SUM(LEN(Sheet1!32769:655 36))

This is an array formula, press Ctrl+Shift+Enter to enter it.

Put the formula on a different sheet to the one you want to count so
that you don't get a circular reference.

And it takes a long time to run - be warned.

Regards

Steve

That is SO cool Steve................
and it also works with a RangeName.........

=SUM(LEN(MyRangeName)) ......still entered as an ArrayFormula.

Vaya con Dios,
Chuck, CABGx3




" wrote:


Jan wrote:
How can I count the number of caracters including spaces in a microsoft excel
worksheet

On a worksheet?

Try:

=SUM(LEN(Sheet1!1:32768))+SUM(LEN(Sheet1!32769:655 36))

This is an array formula, press Ctrl+Shift+Enter to enter it.

Put the formula on a different sheet to the one you want to count so
that you don't get a circular reference.

And it takes a long time to run - be warned.

Regards

Steve

CLR wrote:
That is SO cool Steve................
and it also works with a RangeName.........

=SUM(LEN(MyRangeName)) ......still entered as an ArrayFormula.

Vaya con Dios,
Chuck, CABGx3

Thanks Chuck

=SUM(LEN(Sheet1!1:65536)) would have been nicer but Excel chickens out
for some reason and throws a #NUM! error.

Regards

Steve

CLR - apparently all of the Properties listed as part of the
BuiltInDocumentProperties are not always available to all types of documents.
First, to quote from the Help topic about it:
"Container applications arent required to define values for every built-in
document property. If Microsoft Excel doesnt define a value for one of the
built-in document properties, reading the Value property for that document
property causes an error."

I experimented using the following code:

Sub GetDocumentProperties()
Dim rw As Integer
Dim p As Object
rw = 1
Worksheets("Sheet1").Activate
Range("A1").Select
On Error Resume Next
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
If Err < 0 Then
Cells(rw, 2) = "Error"
Err.Clear
End If
rw = rw + 1
Next
End Sub

and I found that in Excel, these properties returned errors:
Last Print Date (maybe because I've never printed this workbook)
Total Editing Time
Number of Pages
Number of words
Number of characters
Number of bytes
Number of lines
Number of paragraphs
Number of slides
Number of notes
Number of hidden Slides
Number of multimedia clips
Number of characters (with spaces)

Many of those look like they'd be associated with Word or PowerPoint.

"CLR" wrote:

There is a feature of the "BuiltIndocumentProperties" Property in VBA that
will supposedly return the "number of characters (with spaces)", but it does
not respond in my XL97 installation.

hth
Vaya con Dios,
Chuck, CABGx3



"Jan" wrote:

How can I count the number of caracters including spaces in a microsoft excel
worksheet

Hi JL..........yeah, I had the same results..........what version of XL did
you use? I heard some of the later versions handle more of the items, and
also that in the cases where XL does not automatically set some of the items,
sometimes they can be force-set by code........I dunno......it's beyond
me.......
But Steve's thingy in this thread sure is a neat way to solve the OP's
problem, huh.........

Vaya con Dios,
Chuck, CABGx3




"JLatham" wrote:

CLR - apparently all of the Properties listed as part of the
BuiltInDocumentProperties are not always available to all types of documents.
First, to quote from the Help topic about it:
"Container applications arent required to define values for every built-in
document property. If Microsoft Excel doesnt define a value for one of the
built-in document properties, reading the Value property for that document
property causes an error."

I experimented using the following code:

Sub GetDocumentProperties()
Dim rw As Integer
Dim p As Object
rw = 1
Worksheets("Sheet1").Activate
Range("A1").Select
On Error Resume Next
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
If Err < 0 Then
Cells(rw, 2) = "Error"
Err.Clear
End If
rw = rw + 1
Next
End Sub

and I found that in Excel, these properties returned errors:
Last Print Date (maybe because I've never printed this workbook)
Total Editing Time
Number of Pages
Number of words
Number of characters
Number of bytes
Number of lines
Number of paragraphs
Number of slides
Number of notes
Number of hidden Slides
Number of multimedia clips
Number of characters (with spaces)

Many of those look like they'd be associated with Word or PowerPoint.

"CLR" wrote:

There is a feature of the "BuiltIndocumentProperties" Property in VBA that
will supposedly return the "number of characters (with spaces)", but it does
not respond in my XL97 installation.

hth
Vaya con Dios,
Chuck, CABGx3



"Jan" wrote:

How can I count the number of caracters including spaces in a microsoft excel
worksheet