Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to calculate average last N values in a table in Excel
  • How to calculate current stock or inventory in Excel
  • Working With Tables in Excel
  • How to Create Thermometer Chart in Excel
  • Excel Frequency Function Example

References

  • Create hyperlink with VLOOKUP in Excel
  • How to get address of last cell in range in Excel
  • Left Lookup in Excel
  • How to get first column number in range in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation no punctuation
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year

Find longest string in column in Excel

by

This tutorial shows how to Find longest string in column in Excel using the example below;

Formula

{=INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0))}

Explanation

To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is:

{=INDEX(names,MATCH(MAX(LEN(names)),LEN(names),0))}

Where “names” is the named range C5:C14.

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

The key to this formula is the MATCH function, which is set up like this:

MATCH(MAX(LEN(name)),LEN(name),0))

In this snippet, MATCH is set up to perform an exact match by supplying zero for match type. For lookup value, we have this:

MAX(LEN(names))

Here, the LEN function returns an array of results (lengths), one for each name in the list:

{5;6;8;6;6;5;6;9;6;6}

The MAX function then returns the largest value, 9 in this case. For lookup array, LEN is again used to return an array of lengths. The after LEN and MAX run, we have:

MATCH(9,{5;6;8;6;6;5;6;9;6;6},0)

which returns the position of the max value, 8.

This goes into INDEX like this:

=INDEX(names,8)

INDEX duly returns the value in the 8th position of names,  which is “Stephanie”.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • OR function Examples in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • Invoice status with nested if in Excel

Date Time

  • Add days to date in Excel
  • Get day from date in Excel
  • Convert date to Julian format in Excel
  • How to calculate months between dates in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel

General

  • List sheet names with formula in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • Mark Workbook as Final in Excel
  • Subtotal invoices by age in Excel
  • Basic numeric sort formula in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning