+ Reply to Thread
Results 1 to 3 of 3

Displaying longest string in column

  1. #1
    Registered User
    Join Date
    02-10-2014
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    4

    Displaying longest string in column

    Hi all,

    Just wondering how I can show the longest string in a column,
    I've tried to find a formula that does this but it only shows the
    number of characters, not the actual string contents. Any help would be
    greatly appreciated.

    Cheers.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Displaying longest string in column

    I get the feeling there should be a simpler way but this array formula (confirm with ctrl+shift+enter) would do it:
    =INDEX(A1:A300,SUM(IF(LEN(A1:A300)=MAX(LEN(A1:A300)),ROW(A1:A300),0)))

    Adjust 300 to the size of your data set as neccessary.

    Edit: thinking about it, that won't work if there are two strings that both have the same/longest length, try this instead:
    =INDEX(A1:A300,MIN(IF(LEN(A1:A300)=MAX(LEN(A1:A300)),ROW(A1:A300),0)))
    Last edited by ragulduy; 03-13-2014 at 05:45 AM.

  3. #3
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Displaying longest string in column

    Hi
    Maybe this formula would help. Its an array formula, thus hit Ctrl+Shift+Enter
    =INDEX($A$1:$A$700,MATCH(MAX(LEN($A$1:$A$700)),LEN($A$1:$A$700),0))
    Appreciate the help? CLICK *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] My code to find longest string in column doesn't work properly! Need Help!!
    By refree in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2013, 08:27 AM
  2. [SOLVED] How to find longest string in a column
    By refree in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2013, 03:06 AM
  3. [SOLVED] Automatically adjust column width to longest value in column
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2013, 08:18 PM
  4. finding a string in a column, displaying YES on the same row
    By martlume in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-18-2007, 01:16 PM
  5. [SOLVED] Longest string in a column
    By Jeff Kantner in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-01-2005, 02:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1