XL98: Sorting Alphanumeric Text as Numeric Values
ID: Q192899
|
The information in this article applies to:
-
Microsoft Excel 98 Macintosh Edition
SYMPTOMS
In Microsoft Excel, 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.
CAUSE
When Microsoft Excel sorts text, it does so one character at a time from
left to right. For example, if you sort the values 1 and 1A, when these
values are formatted as text, the text with the fewest number of
characters is at the top (1) of the sorted values, while text with the
greatest number of characters is at the bottom (1A). Each character is then
sorted from 0 to 9 and then from A to Z.
For example, suppose you have the following values in a worksheet:
A1: 1
A2: 12
A3: 1A1
A4: 1A2
A5: 2
The desired sort result is 1, 1A1, 1A2, 2, 12. However, the actual result
will be 1, 2, 12, 1A1, 1A2.
WORKAROUND
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 professionals 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/overview/overview.asp
To achieve the desired sort result, use the following Visual Basic custom
function.
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
- Type the following in a worksheet:
A1: 1
A2: 12
A3: 1A1
A4: 1A2
A5: 2
- Select the range A1:A5, and click Sort on the Data menu.
- Under My List Has, click No Header Row, and Sort By Column A in
ascending order.
- The worksheet will be sorted as follows:
A1: 1
A2: 2
A3: 12
A4: 1A1
A5: 1A2
- Enter the following formula into cell B1:
B1: =numsort(A1)
NOTE: This should return a numeric value of 1000 for the number 1
in cell A1.
- With cell B1 selected, click Copy on the Edit menu. Select cells
B2:B5 and click Paste on the Edit menu.
The worksheet should have the following information in it:
A1: 1 B1: 1000
A2: 2 B2: 2000
A3: 12 B3: 12000
A4: 1A1 B4: 1065.001
A5: 1A2 B5: 1065.002
- Select the range A1:B5, and click Sort on the Data menu. Under My List
Has, click No Header Row, and Sort By Column B in ascending order. The
sort should return the following:
A1: 1 B1: 1000
A2: 1A1 B2: 1065.001
A3: 1A2 B3: 1065.002
A4: 2 B4: 2000
A5: 12 B5: 12000
Additional query words:
XL98 alpha-numeric
Keywords : kbdta kbdtacode xlvbahowto
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbprb