Longest string in a column

J

Jeff Kantner

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.
 
R

Ron Coderre

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
 
B

Biff

Hi!

One way:

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

=MAX(LEN(A1:A10))

Biff
 
J

Jeff Kantner

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
M

Myles

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.
 
B

Biff

Hi!

Try this:

Array entered:

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

Biff
 
Top