Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding the longest string (X97 -->)

    Column I contains a list of some 30,000 names. How can I find out the longest string?

    I have used =max(len(I:I)) but it just picks up the cell in that row!!! Any ideas
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Finding the longest string (X97 -->)

    This formula, entered as an array formula (confirm with Ctrl+Shift+Enter), will return the first string with maximum length:
    <code>
    =INDEX(I1:I30000,MATCH(MAX(LEN(I1:I30000)),LEN(I1: I30000),0))
    </code>
    Note: it won't work if you use I:I.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the longest string (X97 -->)

    Hans

    Thanks for your speedy response, it has just resolved a mission critical problem at my end, my contractor will now have to buy the beers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the longest string (X97 -->)

    Hi Jezza, long time no see!

    If you take your formula : =max(len(I1:I30000)) and CTRL + SHIFT +ENTER (as array), it will give you the maximum name length as an integer! (Hans's as you can see will give the longest name!)

    Cheers
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Finding the longest string (X97 -->)

    Can you upload one of the beers through BTP?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding the longest string (X97 -->)

    Hi Rudi

    Been away on holiday for a week and back battling the overdraft.

    Yep you're correct, I forgot the ctrl+shift+enter bit.


    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding the longest string (X97 -->)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •