CODEWITHSUNDEEP
vbaexcelsorting
ataylor
ataylor

Reputation: 35

Sorting Different Length Strings in VBA So That Longest Aren't Last

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

StartRange.Sort Key1:=SortRange, Order1:xlAscending

where StartRange and SortRange are the Range objects defined elsewhere. The function sorts the data correctly, but I get results such as

411
418
422
432
415A
415B
428B

whereas I'd like them to be sorted as:

411
415A
415B
418
422
428B
432

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:

  1. Some of the rooms are four digits (ex. 1195, 1045, etc). None of these rooms have issues with the A or B.
  2. 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).
  3. 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.

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!

Upvotes: 0

Views: 547

Answers (2)

Amorpheuses
Amorpheuses

Reputation: 1423

This following is an adaptation of what's on this page

Sub Sort_Special(StartRange As Range, SortRange As Range)
    Dim i As Long
    With SortRange
      For i = 0 To 9
        .Replace What:=i, Replacement:="ZZZZZ" & i, LookAt:=xlPart, MatchCase:=False
      Next i
    End With

    StartRange.Sort Key1:=SortRange, Order1:=xlAscending, Header:=xlNo

    With SortRange
      For i = 0 To 9
        .Replace What:="ZZZZZ", Replacement:="", LookAt:=xlPart, MatchCase:=False
      Next i
    End With
End Sub

Excel sorts numeric fields ahead of alphanumeric ones. By replacing each digit with a dummy field in which the digit is prefixed with an arbitrary string 'ZZZZZ' you're forcing excel to treat all your fields as alphanumeric. You then do the sort on these converted fields and then afterwards remove each sequence of 'ZZZZZ'.

Hopefully you don't have room #s that end with 5 Zs.

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You can insert a new column and get the numeric values in it and perform your sort on the new columns.

Assuming your data is in column A, then insert a column right to column A with Columns(2).Insert and in the new columns place the following formula

Range("B2:B" & lr).Formula = "=IF(ISERROR(RIGHT(A2,1)*1),LEFT(A2,LEN(A2)-1)*1,A2)"

Where lr is the last row and can be calculated. And now sort on the new column B and delete it in the end.

See if this is something you can work with.

Upvotes: 0

Related Questions