count the number of caracters in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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 said:
How can I count the number of caracters including spaces in a microsoft excel
worksheet
Click to expand...

On a worksheet?

Try:

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

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
 
CLR said:
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
Click to expand...

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 aren’t required to define values for every built-in
document property. If Microsoft Excel doesn’t 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.
 
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
 
Back
Top