# Thread: Finding the longest string (X97 -->)

1. ## 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

2. ## 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. ## 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>

4. ## 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

5. ## Re: Finding the longest string (X97 -->)

Can you upload one of the beers through BTP?

6. ## 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>

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

#### Posting Permissions

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