2

I need to sort data that takes on the format: A1, A2...A10...A?? then starts at B1, B2...B?? and once it gets to Z?? goes to AA1, AA2, AA3...AA?? and then to AB1, AB2, and so on. I never know how many actual digits I may have after the beginning letters but never more that 3 digits worth. The letters never take on more than 2 characters so ZZ would be the highest count. The basic sort treats the cells as text and so sorts it A1, A10, A11-A19, A2 and so on and that does not work for this situation. I am not versed in VBA and I cannot create a custom list as the pins on a component are varied per part. Any help would be much appreciated. Sean

Here is what I am ultimately trying to do - sort column A to the order shown.

Excel screenshot

Improve this question
1
  • You can use PowerShell to achieve this, the sorting order you described is the default sorting logic of many console programs, including PowerShell, they do so because they treat the numbers as strings, they will put A100 before A2(think the digits as letters), if you want such, you can do a simple import-csv, sort-object then export-csv to get what you intend for .csv files, I don't know about .xlsx files though, however it wouldn't be hard, you just need sort-object and it will put things in the order you want.
    – Ξένη Γήινος
    Commented Jan 21, 2021 at 6:17

2 Answers 2

Reset to default
1

If you have O365, you can do this with a single formula:

=SORTBY(myRng,COLUMN(INDIRECT(myRng)),1,ROW(INDIRECT(myRng)),1)

where myRng is the range to sort.

enter image description here

enter image description here

It works using the same algorithm as the splitting seen below, which you can use if you do not have that function.

Split the original data in order to sort the way you want. But I would suggest splitting them by assuming the values represent a cell address.

Then you can have numbers representing both the alpha and numeric parts.

Below, the formulas:

B1: =COLUMN(INDIRECT(A1))
C1: =ROW(INDIRECT(A1))

enter image description here

Sort on Columns B & C:

enter image description here

Is that what you want for a result?

Improve this answer
1
  • Very interesting Ron. Thank you for this. I will play with it tomorrow. Here is ultimately what I am trying to sort. imgur.com/QnVjwGd
    – skelly62
    Commented Jan 20, 2021 at 1:53
0

It appears that you want to sort the data alphanumerically but overide Excel's standard behaviour which sorts shorter text before longer text when the common text is identical.

You can use a "helper" column to pad out the data so they are all the same length, then sort the range by that helper column instead.

You said that the data consists of 1-2 letters followed by 1-3 digits so we'll pad to 6 characters in total (one more than we actually need, just to be safe). We'll also choose a padding character ~ that has an ASCII code preceding those of the digits 0-9 and letters A-Z.

=A2&REPT("~",6-LEN(A2))

Now sort the region by the helper column and you should get order you prefer.

Excel screenshot

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .