Length of contents of the active cell

[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

 

[James Minns]

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

 

[Frank Kabel]

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

 

[Dave R.]

This clearly wont do what the OP is asking for.

 

[Bob Phillips]

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]

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.

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

James

 

[Harlan Grove]

This will return the answer

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

...

So would

=LEN(CELL("Contents"))

with the same [F9] and circular recalc caveats.

I guess only old 123 users grok CELL.

 

[Bob Phillips]

I knew mine was too convoluted<g>.

Bob

This will return the answer

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

..

So would

=LEN(CELL("Contents"))

with the same [F9] and circular recalc caveats.

I guess only old 123 users grok CELL.