Alphabetical Disorder

When an Excel database is sorted by name, you are likely to find the Le Roys, McEnroes and Van Halens fall into odd places. Although Excel disregards apostrophes and hyphens when sorting, it still treats spaces as a character. In conventional sorting they are also disregarded so that there is no problem looking for Vandenberg, Vanden Berg or Van den Berg. They will be treated equally. The same goes for names like McKay and McGregor which are conventionally sorted as if they were MacKay or MacGregor. In an Excel database the two variants would occur in different places (see figure 1).

In a small database the sorting could be done "by hand" but in larger ones it is more practical to let a macro do the job of correcting Excel.

One solution would be to extend the database with one column, copy the names into it, remove the spaces, insert an "a" to turn the Mcs into Macs and assign this column to sort by. Excel will now sort the database by conventional alphabetical sorting.

It can even be done fully automatic by using the Worksheet_Change event.

An Example

Create a new workbook. Open the Visual Basic Editor (Alt+F11), double click Sheet1 in the Project Explorer and insert the following text: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim S As String
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Row
   Case 1, Is > Cells(1).CurrentRegion.Rows.Count
      Exit Sub
   Case Else
      S = WorksheetFunction.Substitute(Target, " ", "")
      If Left(S, 2) = "Mc" Then S = "Mac" & Mid(S, 3)
      Target.Offset(0, 1) = S
End Select
End Sub
Now enter the names of column B in figure 1 (wrong sorting) in column A of Sheet1 and look what happens:

You can download the workbook with the above example: AlphabeticalDisorder.xls.

How does it work?

Every time you enter something in a cell of Sheet1 and then press [Enter], the Worksheet_Change subroutine is called.

If Target.Column <> 1 Then Exit Sub sees to it that whenever you enter something in another column than column A, the subroutine is immediately exited without doing anything (if you want your names in another column, change the 1 accordingly). If a cell in column A is changed, the subroutine continues with:

If Target.Cells.Count > 1 Then Exit Sub checks to see if more than one cell is changed. If so, the subroutine is exited because the subsequent code can only change one cell at the time. If only one cell is changed, the macro continues:

Select Case Target.Row tests for two possibilities:

Case 1, Is > Cells(1).CurrentRegion.Rows.Count checks whether the row of the changed cell is 1 or greater than the current region. If so the subroutine is exited because we want our header to remain as is and when something is entered outside the database we do not want the macro to do anything.

In Case Else we finally come to the code that does the job:

S = WorksheetFunction.Substitute(Target, " ", "") replaces all spaces in the just entered name with nothing, so removing them, and places it in the variable S.

If Left(S, 2) = "Mc" Then S = "Mac" & Mid(S, 3) replaces Mc with Mac.

Target.Offset(0, 1) = S copies the cleaned name in the next column. If you want another column, change the column offset argument (1 in this example) accordingly.

When you are done entering the names, try the following:

Sort the database by column A. You'll see nothing happens because that's the way Excel does it. Not a very good job, is it?

When you sort by column B though, the database is sorted in the conventional way, as we are used to.