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 ??

  • Might this answer help? It's for only one column though, but it's a starting point. – Raystafarian Apr 12 '16 at 16:38
  • @Raystafarian Thanks...............I will take a look. – Gary's Student Apr 12 '16 at 16:44
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

  • 1
    That's pretty crafty. I was wondering why you only comment answers. – Raystafarian Apr 12 '16 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. :) – Scott Craner Apr 12 '16 at 16:48
  • 2
    @ScottCraner...........This is beautiful...............thank you very much! – Gary's Student Apr 12 '16 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. – Raystafarian Apr 12 '16 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 – Raystafarian Apr 12 '16 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.