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

Question

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

Answer 1

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

Answer 2

https://support.microsoft.com/en-gb/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f

https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2#:~:text=VALUE%20function%201%20Description%20Converts%20a%20text%20string,cell%20A1%20of%20a%20new%20Excel%20worksheet.%20

82-8505

(LEFT(A1:A16,2)=82

MID(A1:A16,4,99)=after 4 characters 8505

Do your strings always begin with two characters like 82-8505 rather than 3 or 4 characters like 886-8505 or 8886-8505?

Answer 3

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

The first step is to get the part before the dash using LEFT, but that returns a text. We need a number to sort the "items" as desired, therefore surround with VALUE.

VALUE(LEFT(A1:A16,2))

The second step is to get all chars after the dash and also convert into a number.

VALUE(MID(A1:A16,4,99))

If you would put that formulas into separate columns you can sort all data by these columns and get the result you expect.

You asked just to sort the items without any data in adjacent columns, so we can surround the formulas above with SORTBY.

That's it. Any further questions?

Andreas.

Answer 4

Nope, I see it now. Thank you!!