» How Excel Sorts Lists
CATEGORY - Excel Sorting
VERSION - All Microsoft Excel Versions
Numeric values
Numeric values, including date and time, are sorted from lowest (negative) to highest (positive). Excel does not consider the format of the cell, only its contents.
Date and time are sorted by their numeric values.
Text
Text is sorted first by ASCII characters, such as *, (,), $, and then by letters of the alphabet (also ASCII characters). Uppercase text is sorted before lowercase text.
To sort using case sensitivity:
From the Data menu, select Sort, then Options, and then select the Case Sensitive option.
Logical values
FALSE is sorted before TRUE.
Errors
Errors do not undergo internal sorting, and appear next to last.
Empty cells
Empty cells are always sorted last. Sort Descending changes the sort order from the last to the first, except for empty cells, which are always last.
Book Store:
Sorting Sections and SubSections, IP numbers etc etc
Mych Dubil Posted on: 31-12-1969
Thanks to John Foster from Yorkshire, UK who posted a comment on a way of separating First Names and Surnames....
Select the column with your Section/Subsection numbers or in my case IP numbers and then select Text to Columns from the Data menu. This will set off a wizard that is self explanitory. In the case of Sections/SubSection and IP numbers you would choose Delimitors which in both cases is the"." you then state which column the convertion needs to be placed into.
I was messing around with Formulas, Functions, you name it... all the time the answer was there all the time. Now my problem is trying to sort the IP.... I cant sort over 4 colums so some how I need to make each column 3 characters length (padding out any 1 or 2 characters with "0" in front) and then combine the 4 columns into one and then sort on this column.
Any help would be apprecieated
Sorting Sections and SubSections, IP numbers etc
AS Posted on: 31-12-1969
the concatenate function enables you to combine the text from various cells eg [=CONCATENATE(A8,".",B8,"." etc). Hope this is useful.
Thanks for Mych Dubil for the delimiting tip - very helpful. I guess I'll have to write a macro so that a column will auto-update, unless anyone knows any alternative solutions etc?! Actually I think there may be some text formulaewhich may useful, but I'll look at them some other time
Sorting by date
Russell Posted on: 31-12-1969
I have some linking in MS Excel with date. and I would like to sort it using function. (I cannot sort it by Data>Sort because those are linking and it is different every time when I enter a searchSorting of more than 3 Rows
Shannon Willcott Posted on: 31-12-1969
The sort function in excel only allows me to sort 3 columns at one particular time and I have 7 columns that I need sorted! I tried recording the sort function into a macro and altering it so that it will consider all the columns I have. The macro is as follow but another tip or suggestion would be appreciated!
Sub UWI()
'
' Sort_UWI Macro
Range("D1").Select
Selection.Sort _
Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("E2"), Order2:=xlAscending, _
Key3:=Range("D2"), Order3:=xlAscending, _
Key4:=Range("C2"), Order4:=xlAscending, _
Key5:=Range("B2"), Order5:=xlAscending, _
Key6:=Range("A2"), Order6:=xlAscending, _
Key7:=Range("G2"), Order7:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
Can this be extended so that a list could sort itself:
1
1.1
1.2
1.2.1
....1.2.9
1.2.10
....1.9
1.10
etc etc