How to Sort Naturally in Excel (Item2 before Item10)
Excel's own sort treats your labels as text, so Item10 lands above Item2. Here's how to sort them the way a person actually reads them.
Last updated: August 2026
| Excel's sort | Natural sort | |
|---|---|---|
| Item1, Item10, Item2 | → | Item1, Item2, Item10 |
| file9, file10, file100 | → | file9, file10, file100 |
Method 1 — a helper column (any version)
- In a spare column, pull out the number from each label. For a fixed prefix like
Item:=VALUE(MID(A2,5,10)). - For a mixed prefix, extract the trailing digits, e.g.
=VALUE(TEXTAFTER(A2,"-"))on Excel 365. - Select your data and Sort by the helper column (Data ▸ Sort). Delete the helper column when you're done.
This works, but it breaks the moment your labels don't share one tidy pattern — numbers in the middle, several numbers, or no separator.
Method 2 — Power Query
Load the range as a table, use Split Column ▸ By Non-Digit to Digit to separate text and number, set the number column to Whole Number, sort by text then number, and Close & Load. Robust, but a lot of steps for a one-off sort.
The one-click way — Tellsheet's Natural Sort
Select your data, click Natural Sort, and choose the column to sort by. Numbers inside the text are compared as numbers — so Item2 comes before Item10 and file9 before file10 — blanks fall to the bottom, and you pick ascending or descending. No helper columns, no formulas, and a preview before anything moves. Works on Excel for Windows, Mac, and the web.
Frequently asked questions
Why does Excel sort Item10 before Item2?
Excel compares mixed text character by character, so the 1 in Item10 sorts before the 2 in Item2. A natural sort compares the numbers 10 and 2 instead.
How do I sort file names like file9, file10?
Extract the trailing number into a helper column and sort by it, or use Tellsheet's Natural Sort, which finds numbers anywhere in the text and orders them numerically.
Does natural sort handle blanks?
Yes — in Tellsheet's Natural Sort, blank cells always fall to the bottom, whichever direction you sort.
Related Excel guides
Sort the way people read
Get Tellsheet and natural-sort any column in one click — numbers read as numbers, blanks to the bottom, with a preview before every change.
Get Tellsheet free See pricing