Longest string in a column

Hi,
I'm looking for the function combination to determine, in a column of text,
what is the maximum number of characters in any one cell. max(len(cell))
works for any one cell, but max(len(A:A)) manufactures a number from
somewhere but whatever it is, it's not the number of characters in the
longest cell. Perhaps an array function? But my attempt at that ended with
#NUM!.

Thx.

You actually have the right idea:
=MAX(LEN(A1:A10))

Commit that array formula by holding down the [Ctrl]+[Shift] when you press
[Enter]


Does that help?

***********
Regards,
Ron


"Jeff Kantner" wrote:

> Hi,
> I'm looking for the function combination to determine, in a column of text,
> what is the maximum number of characters in any one cell. max(len(cell))
> works for any one cell, but max(len(A:A)) manufactures a number from
> somewhere but whatever it is, it's not the number of characters in the
> longest cell. Perhaps an array function? But my attempt at that ended with
> #NUM!.
>
> Thx.

Hi!

One way:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=MAX(LEN(A1:A10))

Biff

"Jeff Kantner" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I'm looking for the function combination to determine, in a column of
> text,
> what is the maximum number of characters in any one cell. max(len(cell))
> works for any one cell, but max(len(A:A)) manufactures a number from
> somewhere but whatever it is, it's not the number of characters in the
> longest cell. Perhaps an array function? But my attempt at that ended with
> #NUM!.
>
> Thx.

Thanks v much. I guess the limitation is the reference has to be an
enumerated row range, not just A:A, e.g. I don't necessarily know the number
of rows in the column, but since I'm looking for MAX, an arbitrarily long
(high) number works, the empty cells at the bottom are < MAX so don't affect
the result.

"Ron Coderre" wrote:

> You actually have the right idea:
> =MAX(LEN(A1:A10))
>
> Commit that array formula by holding down the [Ctrl]+[Shift] when you press
> [Enter]
>
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "Jeff Kantner" wrote:
>
> > Hi,
> > I'm looking for the function combination to determine, in a column of text,
> > what is the maximum number of characters in any one cell. max(len(cell))
> > works for any one cell, but max(len(A:A)) manufactures a number from
> > somewhere but whatever it is, it's not the number of characters in the
> > longest cell. Perhaps an array function? But my attempt at that ended with
> > #NUM!.
> >
> > Thx.

On Wed, 30 Nov 2005 15:08:01 -0800, Jeff Kantner
<[email protected]> wrote:

>Hi,
>I'm looking for the function combination to determine, in a column of text,
>what is the maximum number of characters in any one cell. max(len(cell))
>works for any one cell, but max(len(A:A)) manufactures a number from
>somewhere but whatever it is, it's not the number of characters in the
>longest cell. Perhaps an array function? But my attempt at that ended with
>#NUM!.
>
>Thx.

Yes you do need an array function. However, you cannot refer to an entire
column in an array function.

So the array-entered formula =MAX(LEN(A1:A65535)) should work.


--ron

Hi!

Try this:

Array entered:

=INDEX(A1:A5,MATCH(MAX(LEN(A1:A5)),LEN(A1:A5),0))

Biff

"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> Supposing we have the following in a1:a5
>
> ColA
> ------
> CocaCola
> Alfafa
> Oats
> Strawberry
> Wheat
>
> How cam we adapt the array formula to deliver the *longest text*?
> Intuitively, I tried *{=INDEX(A1:A8,MAX(LEN(A1:A10)),1)} *but to no
> avail.
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=489634
>