Sorting Numbers and Text

[noibs]

Assume the following list:

4
6
1
5
M
9
3

What I want is to sort that list in Calc so that the "M" character is considered to be less than the lowest number in the list. Therefore, if the list is sorted in ascending order, the "M" would be first followed by the 1. In descending order, the "M" would be the last item in the list.

However, Calc always sorts with the "M" treated as being larger than any number in the list. Custom sort lists can only contain text, so that doesn't work. Even if i put a zero in front of the M--0M--Calc considers that to be text and sorts it as something larger than the 9.

Any ideas? Enabling "Natural Sort" has no effect.

[Dave]

Perhaps it does so because it IS larger? With a character in A1, put =CODE(A1) in B1 and you'll see.

David.

[acknak]

(Hi Dave! Nice to see you again!)

When you need to modify the sort order of a data set, the most flexible and powerful way to do it is to add a column that contains a sort key computed from the data that you need to sort. Because you compute the sort key, you can produce (almost) any order you want.

And Dave's suggestion would be one way to do it: use CODE() to convert the data into numbers, and adjust the number when there is an "M": =IF(A1="M";0;CODE(A1)) Each digit will give a number from 48 to 57; "M" will give zero. Sort the data on the sort key column and you're done.