Longest string

Try this array formula from J. Walkenbach :-

=ADDRESS(MIN(IF((A3:A11)=MAX(A3:A11),ROW(A3:A11)," ")),COLUMN(A3:A11))

Celia

Ben
The formula I gave was an array formula
and I should have mentioned !! sorry that
it only works if in A2, sorry......I should
have made it more dynamic.
should be;
=ADDRESS(MATCH(MAXA(LEN(A3:A11)),LEN(A3:A11),0)+ROW(),1)

Ivan

Correction

Correction :-

=ADDRESS(MIN(IF(LEN(A3:A11)=MAX(LEN(A3:A11)),ROW(A3:A11)," ")),1)

Try this array formula

=ADDRESS(MATCH(MAXA(LEN(A3:A11)),LEN(A3:A11),1)+ROW(),1)

Note: assumes your column range is A3:A11
change as necc.

HTH

Ivan

Try this array formula =ADDRESS(MATCH(MAXA(LEN(A3:A11)),LEN(A3:A11),1)+ROW(),1) Note: assumes your column range is A3:A11

Thanks Ivan, but I get a #N/A error in the cell.
Is there a typo in the formula?

I was able to figure out a basic macro for column C (finds the first longest.):

Sub FindLongest()
Set longfind = Range("C1")
Set longest = Range("C1")
Do Until longfind = Range("C65536").End(xlUp)
If Len(longfind.Offset(1, 0)) > Len(longest) Then
Set longest = longfind.Offset(1, 0)
End If
Set longfind = longfind.Offset(1, 0)
Loop
longest.Select
End Sub

Ben
Sorry, typo

should be;
=ADDRESS(MATCH(MAXA(LEN(A3:A11)),LEN(A3:A11),0)+ROW(),1)

Will this work ???

Ivan

I don't know if it's me, but I still get the same error.

I tried it and got the error too.
I think it is the MAXA(LEN(A3:A11)) Not returning the right value