XL: Sorting Alphanumeric Text as Numeric Values

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, the value in a cell will be sorted based on its contents. That is, a value formatted as a number will be sorted differently than a number formatted as text. Because of this difference, you may receive unexpected results when you mix numeric and text strings in a sort.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

For example, suppose you have the following values in a worksheet:

   A1:     1
   A2:     12
   A3:     1A1
   A4:     1A2
   A5:     2

The following Visual Basic custom function can be used to perform the desired sort result.

Sample Visual Basic Procedure

Type the following code in a Visual Basic module:

   ' Assume a cell entry of 1A 'myvalue' is the cell reference of the 
   ' number to be sorted.
   Function numsort(myvalue As Variant)

      ' Calculates the function any time the worksheet recalculates
      Application.Volatile

      ' Initializes the variable count as 0
      Count = 0

      ' If the cell is numeric, the variable 'count' is equal to the value 
      ' in the cell times 1000
      ' 1A is not numeric
      If IsNumeric(myvalue) Then
         Count = myvalue * 1000
      Else
         no_text_yet = True

         ' Sets a For-Next loop from 1 to the length of characters in the 
         ' cell.
         ' With 1A, the For-next loop will be from 1 to 2
         For x = 1 To Len(myvalue)

            ' Sets the variable 'current' to the character of position x, 
            ' for a length of 1 character.
            ' The first time through, 'current' will equal 1.
            ' The second time through, 'current' will equal A.
            current = Mid(myvalue, x, 1)
            If IsNumeric(current) Then

               ' If 'current' is numeric, then 'count' is equal to itself
               ' times ten plus 'current'.
               ' The first time through, 'count' is numeric and will equal 
               ' 1.
               ' (0 * 1 + 1).
               ' The second time through, A is not numeric.
               Count = Count * 10 + current

            ' If 'current' is not numeric, then 'count' is equal to itself
            ' times 1000 plus the ASCII value of the letter.
            ' The first time through, 1 is numeric.
            ' The second time through, 'count' equals itself(1) times 1000 
            ' + the ASCII character value of A(65), or 1065
         Else
            Count = Count * 1000 + Asc(current)

            ' Exits the For-Next loop as soon as we reach the first alpha
            ' character
            Exit For
         End If
      Next

      ' If the For-Next loop variable is not equal to the length of 
      ' characters of myvalue, then the last characters must be calculated.
      If x <> Len(myvalue) Then Count = _
         Count + Right(myvalue, Len(myvalue) - x) * 0.001

         ' This will happen as soon as we encounter the first alpha 
         ' character. In this is the case, 'count' equals itself plus the 
         ' right character of the total length minus the For-Next variable 
         ' "x".
      End If
      numsort = Count
   End Function

To Use the Custom Function