title: Sorting Different Length Strings in VBA So That Longest Aren't Last
body: <p>I'm trying to sort a list of room numbers using VBA. However, some of the rooms have an "A" or a "B" at the end of the number to denote suite style living. I want to sort the list of rooms so that the ones with an A or a B aren't sorted to the end of the list because they're longer strings. I tried the sort function in VBA like so</p>
  
  <pre><code>StartRange.Sort Key1:=SortRange, Order1:xlAscending
  </code></pre>
  
  <p>where StartRange and SortRange are the Range objects defined elsewhere. The function sorts the data correctly, but I get results such as</p>
  
  <pre><code>411
  418
  422
  432
  415A
  415B
  428B
  </code></pre>
  
  <p>whereas I'd like them to be sorted as:</p>
  
  <pre><code>411
  415A
  415B
  418
  422
  428B
  432
  </code></pre>
  
  <p>I thought about trying to sort the rooms as text by only counting the first three characters and then sorting each individual block of As and Bs depending on the last letter, but this is further complicated by three things:</p>
  
  <ol>
  <li>Some of the rooms are four digits (ex. 1195, 1045, etc). None of these rooms have issues with the A or B.</li>
  <li>Some of the rooms are prefaced by a "W" or an "E" depending on if they are in the West or East half of the building. Some of these rooms do have the A or B suffix, but they are sorted correctly in the list the way I would like them to be sorted (ex. W134, W135A, W135B, W136, and so on).</li>
  <li>Not all of the rooms end up in the same sheet in Excel. I'm working from a master list of rooms from several different buildings, and each sheet is sorted by building.</li>
  </ol>
  
  <p>The format of the cells has been both "general" and "text" and both have had the same result. Any help that can be offered would be much appreciated in solving this issue. Thank you in advance! </p>