Sort does not work properly

Deleted
Not applicable

Pretext: I know this has been done to death, but my problem is different.

 

At work there is a document which is used for organizing inventory. There is a column called "Location" (A1) where they go 1A, 1B, 1C, 1D, 2A, 2B, 2C, ...., 120A, 120B, 120C, where the last character indicates the vertical level where a forklift stores a pallet loaded with inventory. Not all numbers have same number of levels. They got sorted out of this order, and now I cannot sort them back. No matter what I do, it will only sort these numbers (after removing the last character, which is always text) by first sorting the numbers with 3 digits, then the numbers with 2 come after, then the numbers with only 1 digit, whether smallest to largest or vise versa.

 

As I mentioned, I removed the last character in another column (Location1) so to make them all numbers. Isnumber returns true for all cells. I also used the clean function, and tried multiplying by 1. Last, I also forced all these cells to have 3 digits, e.g.1 would be 001, and so on. When I manually type some of these kinds of numbers (1, 9, 10, 19, 100, 120...) and sort, it works, but for some reason it won't do it to the cells under Location1, that manipulates cells under Location.

 

I got some of the ideas from above from google searches, but I don't have any more things left to try. Any help is appreciated. They use Excel 2016, and there is no possibility for me to show this document on this forum.

1 Reply

@Deleted 

You could either replace the column or add a column with leading 0s in order to make the alphabetical order line up with numerical ordering.

= REPT(0,4-LEN(location))&location

Sorting on the new column (either the SORT function in 365 or manual sort in older versions) will restore your original ordering.