Length of contents of the active cell

  • Thread starter Thread starter Michael J. Malinsky
  • Start date Start date
M

Michael J. Malinsky

=LEN(H5)

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Hi all!

I have a worksheet with text in many cells.
I would like to show in cell A1 the length of the text in the selected cell.

So if the user clicks cell H5 which contains the text "James", cell A1
should show 5. Is it possible?
If possible, preferably without VBA.

Thanks for any ideas,

James Minns
Italy
 
Hi
without VBA not possible. With VBA you may put the following code in
your worksheet module (not in a standard module):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo sub_exit
Range("A1").value = len(Cstr(target.value))

sub_exit:
Application.EnableEvents = True
End Sub
 
Hi James,

This will return the answer

=LEN(INDIRECT(CELL("address")))

but .... you have to hit F9 to update it, it won't update automatically when
the activecell changes, and you will get a circular reference if the cell
with this formula is active when you re-calculate.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
James Minns said:
I have a worksheet with text in many cells.
I would like to show in cell A1 the length of the text in the selected
Click to expand...
cell.

Thanks to everyone who replied, I'm going with the VBA solution.

James
 
This will return the answer

=LEN(INDIRECT(CELL("address")))
Click to expand...
...

So would

=LEN(CELL("Contents"))

with the same [F9] and circular recalc caveats.

I guess only old 123 users grok CELL.
 
I knew mine was too convoluted<g>.

Bob

Harlan Grove said:
This will return the answer

=LEN(INDIRECT(CELL("address")))
Click to expand...
..

So would

=LEN(CELL("Contents"))

with the same [F9] and circular recalc caveats.

I guess only old 123 users grok CELL.
Click to expand...
 
Back
Top