Dynamic Arrays
Sort text by character length (with alphabetical tie-breaking in SORTBY)
Order product SKUs, ad copy, or domain names from shortest to longest (or longest first) and break equal-length ties A–Z using two-level SORTBY.
Combine character length and alphabetical tie-breaking in SORTBY
Standard A–Z sorting places "SKU-1000" ahead of "SKU-9" because character "1" precedes "9" in this text ordering—even though "SKU-9" is shorter!
Adding a CharLen column (=LEN(A2)) and passing TWO sort keys into SORTBY(A2:A5, B2:B5, 1, A2:A5, 1) sorts primarily by character count ascending (4 chars before 6 chars) and breaks equal-length ties alphabetically ("kiwi" vs "plum" -> "kiwi" then "plum")!
| Keyword (A) | Length (B) | Sorted Shortest -> Longest, A–Z |
|---|---|---|
| plum | 4 | 1. kiwi (4 chars, K before P) |
| banana | 6 | 2. plum (4 chars) |
| kiwi | 4 | 3. banana (6 chars) |
| strawberry | 10 | 4. strawberry (10 chars) |
Shortest-first and longest-first multi-key SORTBY formulas
Pass 1 for shortest-to-longest or -1 for longest-to-shortest on the length column while keeping alphabetical tie-breaking at 1.
=SORTBY(A2:A5, B2:B5, 1, A2:A5, 1)Sort longest strings first (descending length, A–Z ties)
Flips the first sort_order argument from 1 to -1.
=SORTBY(A2:A5, B2:B5, -1, A2:A5, 1)Additional worked calculation
Using the initial example data:
=TEXTJOIN(", ", TRUE, SORTBY(A2:A5, B2:B5, 1, A2:A5, 1))Additional worked calculation
Using the initial example data:
=TEXTJOIN(", ", TRUE, SORTBY(A2:A5, B2:B5, -1, A2:A5, 1))Additional worked calculation
Using the initial example data:
=INDEX(SORTBY(A2:A5, B2:B5, 1, A2:A5, 1), 1, 1)Inputs and interpretation
LEN is not byte length or rendered width. Unicode handling and alphabetical collation can differ by spreadsheet version and locale. Use aligned sort keys; blanks and duplicates are retained unless filtered out. The unwrapped SORTBY formulas return separate rows.
Try the example
Use the original values or change them to test your understanding.