Alpha-Numeric Sorting

[Jose]

The ascend sort return my list as:
EXCEL SORT WOULD PREFER THIS
R10-1 R1-1
R10-10L R1-2
R10-2 R10-1
R1-1 R10-2
R11-1 R10-10L
R1-2 R11-1
R12-1 R12-1

I would prefer the sort from the second column. The second column was
assembled manually.

 

[Sloth]

I don't think you can change the sorting options, but if you insert your
information like this it will sort the way you want.

R01-01
R01-02
R10-01
R10-02
R10-10L
R11-01
R12-01

Another option would be to insert the information as time and use a custom
format of
R[h]-m
but you would have to change the one with the L to a custom format of
R[h]-mL
Then it would look and sort the way you want.

R1-1 - Inserted as 1:01
R1-2 - Inserted as 1:02
R10-1 - Inserted as 10:01
R10-2 - Inserted as 10:02
R10-10L - Inserted as 10:10 with secondary format type
R11-1 - Inserted as 11:01
R12-1 - Inserted as 12:01

 

[Ron Rosenfeld]

The ascend sort return my list as:
EXCEL SORT WOULD PREFER THIS
R10-1 R1-1
R10-10L R1-2
R10-2 R10-1
R1-1 R10-2
R11-1 R10-10L
R1-2 R11-1
R12-1 R12-1

I would prefer the sort from the second column. The second column was
assembled manually.

If I understand you correctly, you want to sort numerically first by the
numbers that are between the "R" and the "-", and then by the numbers that are
after the "-". The latter numbers may, on occasion, be followed by a letter.

I would set up two "helper columns" in which you extract the sort keys.

If your data is in Column F, then

G1: =--MID(F1,2,FIND("-",F1)-2)
H1: =LOOKUP(9.99E+300,--MID(F1,FIND("-",F1)+1,ROW(INDIRECT("1:5"))))

Select the entire range to be sorted, in this case F1:H7, and select Data/Sort
and do an ascending sort first on column G and then on Column H.


--ron

 

[Jose]

Thanks Ron, your recommendation was right on key. The issue was sorting
across the hyphen with what I had. Thanks all.

 

[Jose]

Thanks Ron.
--
Regards

If I understand you correctly, you want to sort numerically first by the
numbers that are between the "R" and the "-", and then by the numbers that are
after the "-". The latter numbers may, on occasion, be followed by a letter.

I would set up two "helper columns" in which you extract the sort keys.

If your data is in Column F, then

G1: =--MID(F1,2,FIND("-",F1)-2)
H1: =LOOKUP(9.99E+300,--MID(F1,FIND("-",F1)+1,ROW(INDIRECT("1:5"))))

Select the entire range to be sorted, in this case F1:H7, and select Data/Sort
and do an ascending sort first on column G and then on Column H.


--ron

 

[Ron Rosenfeld]

Thanks Ron, your recommendation was right on key. The issue was sorting
across the hyphen with what I had. Thanks all.

Glad it worked for you. Thanks for the feedback.

--ron