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.

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.

Hi!

One way:

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

=MAX(LEN(A1:A10))

Biff

"Jeff Kantner" wrote in message
...
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
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

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

Hi!

Try this:

Array entered:

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

Biff

"Myles" wrote in
message ...

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