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

J

jonathan92591

Board Regular
Joined
Oct 27, 2011
Messages
65
[TABLE="width: 1341"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Excel or Access version:[/TD]
[TD]Excel 2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Computer operating system:[/TD]
[TD]Windows 7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample data:[/TD]
[TD]Sheet1

*ABCD
C1C2C3C4
ABCABCB07-1B7
ABCABCB07-10B7
ABCABCB07-10AB7
ABCABCB07-10B10BB7
ABCABCB07-11B7
ABCABCB07-11AB7
ABCABCB07-2B7
ABCABCB07-2AB7
ABCABCB07-2B2BB7
ABCABCB07-2C2CB7
ABCABCB07-3B7
ABCABCB07-3AB7
ABCABCB07-3B3BB7
ABCABCB07-4B7
ABCABCB07-4AB7
ABCABCB07-4B4BB7
ABCABCB07-5B7
ABCABCB07-6B7
ABCABCB07-6AB7
ABCABCB07-7B7
ABCABCB07-7AB7
ABCABCB07-7B7BB7
ABCABCB07-7C7CB7
ABCABCB07-8B7
ABCABCB07-9B7
ABCABCB07-9AB7
ABCABCB07-9B9BB7

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]10A[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]11A[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]3A[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]4A[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]6A[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]7A[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="align: right"]9A[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula(s) right now:[/TD]
[TD]Not applicable, only using the sort function.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current result(s):[/TD]
[TD]I sort column B lowest to highest.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]My goal:[/TD]
[TD]To get the data to sort starting with after the "-" and going in order of number first then letter.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]What I want it to look like:[/TD]
[TD]Sheet1

*ABCD
C1C2C3C4
ABCABCB07-1B7
ABCABCB07-2B7
ABCABCB07-2AB7
ABCABCB07-2B2BB7
ABCABCB07-2C2CB7
ABCABCB07-3B7
ABCABCB07-3AB7
ABCABCB07-3B3BB7
ABCABCB07-4B7
ABCABCB07-4AB7
ABCABCB07-4B4BB7
ABCABCB07-5B7
ABCABCB07-6B7
ABCABCB07-6AB7
ABCABCB07-7B7
ABCABCB07-7AB7
ABCABCB07-7B7BB7
ABCABCB07-7C7CB7
ABCABCB07-8B7
ABCABCB07-9B7
ABCABCB07-9AB7
ABCABCB07-9B9BB7
ABCABCB07-10B7
ABCABCB07-10AB7
ABCABCB07-10B10BB7
ABCABCB07-11B7
ABCABCB07-11AB7

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2A[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]3A[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]4A[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]6A[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]7A[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]9A[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]10A[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="align: right"]11A[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Error message:[/TD]
[TD]No error message[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]How error occurred:[/TD]
[TD]No error message[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Generated in:[/TD]
[TD]Excel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thank you.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
this solution with helper columns E & F (sort E > smallest to largest and F > A to Z);

Excel 2012
ABCDEF
C1C2C3C4
ABCABCB07-1B7
ABCABCB07-2B7
ABCABCB07-2A2AB7A
ABCABCB07-2B2BB7B
ABCABCB07-2C2CB7C
ABCABCB07-3B7
ABCABCB07-3A3AB7A
ABCABCB07-3B3BB7B
ABCABCB07-4B7
ABCABCB07-4A4AB7A
ABCABCB07-4B4BB7B
ABCABCB07-5B7
ABCABCB07-6B7
ABCABCB07-6A6AB7A
ABCABCB07-7B7
ABCABCB07-7A7AB7A
ABCABCB07-7B7BB7B
ABCABCB07-7C7CB7C
ABCABCB07-8B7
ABCABCB07-9B7
ABCABCB07-9A9AB7A
ABCABCB07-9B9BB7B
ABCABCB07-10B7
ABCABCB07-10A10AB7A
ABCABCB07-10B10BB7B
ABCABCB07-11B7
ABCABCB07-11A11AB7A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"]11[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]11[/TD]

</tbody>
Sheet3​
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(ISTEXT(C2),VALUE(LEFT(C2,LEN(C2)-1)),C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(ISTEXT(C2),RIGHT(C2,1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
copy/paste table using VBA
Replies
5
Views
490
Dnk_83
D
Z
  • Solved
Parent-Child Relationships for a BOM
Replies
8
Views
3K
Peter_SSs
Peter_SSs
S
  • Question Question
Complex List Generator- Multiple Lists, Multiple Choose N's, Millions of Results
2 3
Replies
21
Views
2K
SuperNerd
S
gravanoc
  • Solved
Best way to increment by letters after number 26
Replies
2
Views
1K
shg
shg
A
  • Question Question
Help!!!!!!!!
Replies
7
Views
1K
Abhilash1503
A

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top