|
The SORT.COMPARE() function compares two items using a specified set of comparison rules.
Format
SORT.COMPARE(string1, string2, mode, no.case)
where
| string1 | is the first string to be compared. |
| string2 | is the second string to be compared. |
| mode | identifies the manner in which the comparison is to be performed. |
| no.case | a boolean value indicating whether the comparison should be case insensitive. |
The SORT.COMPARE() function compares string1 and string2 according to the sort rules specified by the mode value.
| Mode 0 | A simple left justified comparison, examining corresponding characters from the start of each string until either a difference is found or the end of both strings has been reached. |
| Mode 1 | A simple right justified comparison in which the shorter item is effectively padded with leading spaces and the resultant strings are compared character by character from the start until either a difference is found or the end of both strings has been reached. If both strings can be treated as integer values, possibly with a leading sign character, a numeric comparison is performed. |
| Mode 2 | A partitioned sort in which the two strings are considered to be formed from a series of alternating numeric and non-numeric elements. Numeric elements are sorted into numerical value, non-numeric elements are sorted into collating sequence order. If the first element is numeric, it may have an optional leading sign character. Sign characters appearing later in the strings are treated as being non-numeric characters. |
| Mode 3 | The same as mode 1 except that the test for numeric items allows non-integer values. |
In all cases, the no.case argument can be used to specify that alphabetic items should be treated as case insensitive, effectively replacing lower case letters with their upper case equivalents.
The SORT.COMPARE() function returns
| -1 | string1 comes before string2 |
| 0 | string1 is equal to string2 |
| 1 | string1 comes after string2 |
Examples
Using modes the modes indicated below, the effect of sorting GC5, ND620, GC41 and CD631 would be
| Mode 0 | CD631, GC41, GC5, ND620 |
| Mode 1 | GC5, GC41, CD631, ND620 |
| Mode 2 | CD631, GC5, GC41, ND620 |
See also:
COMPARE()
|
