title: Alphanumeric sorting in Excel using VBA
body: <p>I use create Bills Of Materials in Excel before importing into another program and I'm trying to sort these lists by Reference Designation which consist of prefix page number 1-200,  followed by device type A-ZZ, followed by device number (ID) on that page 1-99, followed by letter which represents part of device A-Z (if that device consists of multiple parts).  <br/>Here are examples of RefDes:<br/></p>
  
  <pre><code>1Q1
  1S6
  1S7
  1T1
  1VENT
  1X1
  1X2
  1Y1
  1Z1-A
  1Z1-B
  2A1-A
  2A1-B
  2A1-C
  22M1
  2QF1
  2RB1
  2Z1-A
  2Z1-B
  13A1-A
  13A1-B
  13A1-C
  3A2-A
  3A2-B
  3A2-C
  </code></pre>
  
  <p>I want it sorted first by Device type A-ZZ, then by device ID, then device part, then by page.</p>
  
  <pre><code>1A1A
  ....
  1A1Z
  1A2A
  ....
  1A2Z
  2A1A
  ....
  2A1Z
  ....
  200A99Z
  1B1A
  ....
  200ZZ99Z
  </code></pre>
  
  <p>So that the list above is sorted like this:</p>
  
  <pre><code>2A1-A
  2A1-B
  2A1-C
  3A1-A
  3A1-B
  3A1-C
  13A1-A
  13A1-B
  13A1-C
  22M1
  1Q1
  2QF1
  2RB1
  1S6
  1S7
  1T1
  1VENT
  1X1
  1X2
  1Z1-A
  1Z1-B
  2Z1-A
  2Z1-B
  </code></pre>
  
  <p>So far I have been able to sort by page then by device type and even then it sorts wrong: 1A-1ZZ, then 1xA-1xZZ, then 2A-2ZZ, then 2xA-2xZZ, etc... I can get rid of the dash (-) when I build the BOM.</p>