Text
Find the longest or shortest text string in a column (LEN + XLOOKUP)
Audit maximum character width before database imports and retrieve the exact longest or shortest string overall or by category.
Audit text lengths before import
Check source lengths against the destination schema before importing. Depending on the database and import settings, oversized text may be rejected or truncated.
| Region (A) | CityName (B) | CharLen = LEN(B) (C) |
|---|---|---|
| East | NY | 2 (Shortest overall) |
| West | San Francisco | 13 (Longest overall) |
| South | Austin | 6 |
| East | Philadelphia | 12 (Longest in East) |
Retrieve longest string, shortest string, and category max length
When two strings tie for maximum length, XLOOKUP returns the first match from top to bottom (or last match with search_mode = -1).
=XLOOKUP(MAX(C2:C5), C2:C5, B2:B5)Max character length within a specific region (MAXIFS)
Finds the longest character count strictly among "East" rows.
=MAXIFS(C2:C5, A2:A5, "East")Additional worked calculation
Using the same initial input cells:
=XLOOKUP(MIN(C2:C5), C2:C5, B2:B5)Inputs and interpretation
LEN measures text length under the engine’s character convention, not encoded byte length or displayed width. Unicode handling can differ across spreadsheet versions. Decide whether blanks count as shortest strings. MAXIFS returning zero for no matching category does not identify an actual matching record. Validate against the destination field’s actual encoding and length rules.
Try the example
Use the original values or change them to test your understanding.