Lookup & Reference
Sort SKUs and composite codes by an embedded substring or middle number (SORTBY)
Sort compound identifiers like "WH-1050-EUR" and "ZR-0015-USA" by their middle part number or trailing region suffix using TEXTBEFORE, TEXTAFTER, and SORTBY.
Sort compound codes by the part that actually matters—without splitting your output!
Warehouse SKUs like "WH-1050-EUR", "AX-0080-USA", "BL-0420-APAC", and "ZR-0015-USA" start with a facility prefix. Plain SORT orders them alphabetically by prefix ("AX", "BL", "WH", "ZR") rather than numerically by part number (15, 80, 420, 1050)!
Extracting the numeric middle token into a sort-key helper B2 = VALUE(TEXTBEFORE(TEXTAFTER(A2, "-"), "-")) and passing B2:B5 into SORTBY(A2:A5, B2:B5, 1) spills the intact original SKUs ordered cleanly from 0015 up to 1050!
| Raw SKU (A) | Middle Part # Key (B) | Region Suffix Key (C) | Sorted by Part # Ascending |
|---|---|---|---|
| "ZR-0015-USA" | 15 | "USA" | 1st: ZR-0015-USA |
| "AX-0080-USA" | 80 | "USA" | 2nd: AX-0080-USA |
| "BL-0420-APAC" | 420 | "APAC" | 3rd: BL-0420-APAC |
| "WH-1050-EUR" | 1050 | "EUR" | 4th: WH-1050-EUR |
Sort intact SKUs ascending by embedded middle part number
B2:B5 holds =VALUE(TEXTBEFORE(TEXTAFTER(A2, "-"), "-")).
The result spills all four codes; leave four output cells free. Each source code must have exactly the documented prefix-number-region shape, with a numeric middle token. Malformed codes should be corrected, not silently treated as zero.
=SORTBY(A2:A5,B2:B5,1)Two-level sort: Region suffix first, then part number ascending
C2:C5 holds =TEXTAFTER(A2, "-", 2); SORTBY sorts by Region (APAC, EUR, USA) and breaks ties within USA by part number (15 before 80)!
Leading zeros disappear in the numeric sort key but stay in the original code. Equal keys have no additional business tie-breaker here; add an explicit unique key if a prescribed order is required. The four-row ranges do not automatically include appended rows.
=SORTBY(A2:A5,C2:C5,1,B2:B5,1)Try the example
Use the original values or change them to test your understanding.