5

I have a small block of cells, say C4 through G11. The cells contain text of various lengths. I can easily get the length of the longest string in the block with the array formula:

=MAX(LEN(C4:G11))


enter image description here

I need a formula to get the address of the cell with this longest string. If there is more than one cell with the longest string, I need the address of the cell closest to the top the the block. If the is more than one cell with the longest string in the same row, I need the address of the one closest to the left edge of the block.

In the above example the formula should return E8.

For this workbook, I can't use VBA. Any suggestions ??

2
  • Might this answer help? It's for only one column though, but it's a starting point. Apr 12, 2016 at 16:38
  • @Raystafarian Thanks...............I will take a look. Apr 12, 2016 at 16:44

1 Answer 1

7

Because it's Gary's Student:

=ADDRESS(MIN(IF(LEN($C$4:$G$11)=MAX(LEN($C$4:$G$11)),ROW($C$4:$G$11))),MIN(IF(LEN($C$4:$G$11)=MAX(LEN($C$4:$G$11)),COLUMN($C$4:$G$11))),4)

It is an array formula so Ctrl-Shift-Enter.

enter image description here

8
  • 1
    That's pretty crafty. I was wondering why you only comment answers. Apr 12, 2016 at 16:46
  • 1
    @Raystafarian I am generally a lazy person and if a comment will answer the question then I will use the shortest route. :) Apr 12, 2016 at 16:48
  • 2
    @ScottCraner...........This is beautiful...............thank you very much! Apr 12, 2016 at 16:55
  • 1
    Ah, I figured it was something like that, I guess that's because the row formula is being used as an array as well? So it's going {4,4,4,4,4,5,5,5,5,5 ...? That is beautiful. Apr 12, 2016 at 17:07
  • 1
    And the column is going {3,4,5,6,7,3,4,5,6,7} I feel ignorant now. Anyway you got my +1 Apr 12, 2016 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .