Finding the cell with the most characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

arcandio@gmail.com - 15 Dec 2007 00:22 GMT

Alright, fir one of my current projects, I need to output the contents
of the cell with the most text in it from a column. How would I do
this? I've tried arrays based on LEN, but I get the number of
characters instead of the cell contents.

Reply to this Message

Ron Rosenfeld - 15 Dec 2007 01:41 GMT

>Alright, fir one of my current projects, I need to output the contents
>of the cell with the most text in it from a column. How would I do
>this? I've tried arrays based on LEN, but I get the number of
>characters instead of the cell contents.

This *array* formula (enter with <ctrl><shift><enter>) should return the
contents of the cell with the most characters. Rng refers to the appropriate
column (e.g. rng refers to: A1:A100).

=INDEX(rng,MATCH(MAX(LEN(rng)),LEN(rng),0))

--ron

Reply to this Message

arcandio@gmail.com - 15 Dec 2007 22:00 GMT

> On Fri, 14 Dec 2007 16:22:00 -0800 (PST), "arcan...@gmail.com"
>
[quoted text clipped - 11 lines]
>
> --ron

Thanks very much Ron! I actually ended up solving the thing by adding
each cell to the one above it till it got to the top. This will help a
lot in other parts though!

Reply to this Message

Rate this thread: