How to sort alphanumeric text after dash starting with number then letter

[jonathan92591]

--
Excel or Access version:	Excel 2013
Computer operating system:	Windows 7
Sample data:	Sheet1 * A B C D 1 C1 C2 C3 C4 2 ABC ABCB07-1 1 B7 3 ABC ABCB07-10 10 B7 4 ABC ABCB07-10A 10A B7 5 ABC ABCB07-10B 10B B7 6 ABC ABCB07-11 11 B7 7 ABC ABCB07-11A 11A B7 8 ABC ABCB07-2 2 B7 9 ABC ABCB07-2A 2A B7 10 ABC ABCB07-2B 2B B7 11 ABC ABCB07-2C 2C B7 12 ABC ABCB07-3 3 B7 13 ABC ABCB07-3A 3A B7 14 ABC ABCB07-3B 3B B7 15 ABC ABCB07-4 4 B7 16 ABC ABCB07-4A 4A B7 17 ABC ABCB07-4B 4B B7 18 ABC ABCB07-5 5 B7 19 ABC ABCB07-6 6 B7 20 ABC ABCB07-6A 6A B7 21 ABC ABCB07-7 7 B7 22 ABC ABCB07-7A 7A B7 23 ABC ABCB07-7B 7B B7 24 ABC ABCB07-7C 7C B7 25 ABC ABCB07-8 8 B7 26 ABC ABCB07-9 9 B7 27 ABC ABCB07-9A 9A B7 28 ABC ABCB07-9B 9B B7 <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:79px;"><col style="width:30px;"><col style="width:180px;"></colgroup><tbody> </tbody> Excel tables to the web >> Excel Jeanie HTML 4
Formula(s) right now:	Not applicable, only using the sort function.
Current result(s):	I sort column B lowest to highest.
My goal:	To get the data to sort starting with after the "-" and going in order of number first then letter.
What I want it to look like:	Sheet1 * A B C D 1 C1 C2 C3 C4 2 ABC ABCB07-1 1 B7 3 ABC ABCB07-2 2 B7 4 ABC ABCB07-2A 2A B7 5 ABC ABCB07-2B 2B B7 6 ABC ABCB07-2C 2C B7 7 ABC ABCB07-3 3 B7 8 ABC ABCB07-3A 3A B7 9 ABC ABCB07-3B 3B B7 10 ABC ABCB07-4 4 B7 11 ABC ABCB07-4A 4A B7 12 ABC ABCB07-4B 4B B7 13 ABC ABCB07-5 5 B7 14 ABC ABCB07-6 6 B7 15 ABC ABCB07-6A 6A B7 16 ABC ABCB07-7 7 B7 17 ABC ABCB07-7A 7A B7 18 ABC ABCB07-7B 7B B7 19 ABC ABCB07-7C 7C B7 20 ABC ABCB07-8 8 B7 21 ABC ABCB07-9 9 B7 22 ABC ABCB07-9A 9A B7 23 ABC ABCB07-9B 9B B7 24 ABC ABCB07-10 10 B7 25 ABC ABCB07-10A 10A B7 26 ABC ABCB07-10B 10B B7 27 ABC ABCB07-11 11 B7 28 ABC ABCB07-11A 11A B7 <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:79px;"><col style="width:30px;"><col style="width:180px;"></colgroup><tbody> </tbody> Excel tables to the web >> Excel Jeanie HTML 4
Error message:	No error message
How error occurred:	No error message
Generated in:	Excel
Thank you.
JT

<tbody>
</tbody>

 

[AlanY]

this solution with helper columns E & F (sort E > smallest to largest and F > A to Z);

Excel 2012

	A	B	C	D	E	F
1	C1	C2	C3	C4
2	ABC	ABCB07-1	1	B7	1
3	ABC	ABCB07-2	2	B7	2
4	ABC	ABCB07-2A	2A	B7	2	A
5	ABC	ABCB07-2B	2B	B7	2	B
6	ABC	ABCB07-2C	2C	B7	2	C
7	ABC	ABCB07-3	3	B7	3
8	ABC	ABCB07-3A	3A	B7	3	A
9	ABC	ABCB07-3B	3B	B7	3	B
10	ABC	ABCB07-4	4	B7	4
11	ABC	ABCB07-4A	4A	B7	4	A
12	ABC	ABCB07-4B	4B	B7	4	B
13	ABC	ABCB07-5	5	B7	5
14	ABC	ABCB07-6	6	B7	6
15	ABC	ABCB07-6A	6A	B7	6	A
16	ABC	ABCB07-7	7	B7	7
17	ABC	ABCB07-7A	7A	B7	7	A
18	ABC	ABCB07-7B	7B	B7	7	B
19	ABC	ABCB07-7C	7C	B7	7	C
20	ABC	ABCB07-8	8	B7	8
21	ABC	ABCB07-9	9	B7	9
22	ABC	ABCB07-9A	9A	B7	9	A
23	ABC	ABCB07-9B	9B	B7	9	B
24	ABC	ABCB07-10	10	B7	10
25	ABC	ABCB07-10A	10A	B7	10	A
26	ABC	ABCB07-10B	10B	B7	10	B
27	ABC	ABCB07-11	11	B7	11
28	ABC	ABCB07-11A	11A	B7	11	A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =IF(ISTEXT(C2),VALUE(LEFT(C2,LEN(C2)-1)),C2) F2 =IF(ISTEXT(C2),RIGHT(C2,1),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jonathan92591]

Perfect! Thank you AlanY.

 

[AlanY]

you're welcome