Share via

Excel Sorting by text, not number (10 sorted before 2)

Anonymous
2023-04-28T00:11:23+00:00

Hello,

I am trying to sort file numbers that include the year and have been unsuccessful in getting it to sort by number rather than text. I tried changing the value from general & text to number with no luck. I tried text to column. I even tried a Value(Mid(Find)) formula.

Any help is appreciated.

Here's a sample of the number that refuse to get in line. Depending on who completed the data entry, some years have leading zeros and others don't.

82-8505

82-8623

82-9065

82-9298

83-1160

83-1306

83-1390

83-18170

83-2056

83-2438

83-3286

83-9210

83-9406

84-0407

84-07636

84-07668

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Andreas Killer 144K Reputation points Volunteer Moderator
    2023-04-28T14:22:46+00:00

    B1: =SORTBY(A1:A16,VALUE(LEFT(A1:A16,2)),1,VALUE(MID(A1:A16,4,99)),1)

    1 person found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-28T00:48:47+00:00
    f01
    82-8505
    82-8623
    82-9065
    82-9298
    83-1160
    83-1306
    83-1390
    83-2056
    83-2438
    83-3286
    83-9210
    83-9406
    83-18170
    84-0407
    84-07636
    84-07668

    Hi,

    Expected result like above?

    0 comments
  2. Anonymous
    2023-04-28T14:09:42+00:00

    Yes, that's exactly what I'm trying to accomplish!

    0 comments
  3. Anonymous
    2023-04-28T14:19:10+00:00

    Copy to new column. Then Text to Column with "-"

    Sort 3 then Sort 2.

    0 comments
  4. Anonymous
    2023-04-28T14:55:22+00:00

    Thank you!!! I think I understand most of the formula, but would you mind explaining how it works?

    0 comments