For instance, I would like these 6 numbers.
Currently when I use the sort function it puts 6.6.1.1.13 first and 6.6.1.1.2 later.
Before Sort
6.6.1.1
6.6.1.1.1
6.6.1.1.13
6.6.11.14
6.6.1.1.2
What I Want It To Look Like After Sort
6.6.1.1
6.6.1.1.1
6.6.1.1.2
6.6.1.1.13
6.6.11.14
CodePudding user response：
You could make a helper column in which you remove the points and sort that helper column.
=NUMBERVALUE(SUBSTITUTE(E4;".";))
CodePudding user response：
Manual method
Use the Text to Column function and separate out your headers using "." as a delimiter.
When you are done select all the data as follows:
Perform a sort on the selected data.
Note: My data has headers has been selected and column 6 and 7 come up a A to Z as they are currently empty and it defaults to alphabetical sort as a result. The alphabetical sort can be added by adding a dummy row of data at the start or end of your data to be sorted. This is done by either adding all 0's or a number larger than any number in your list to all columns.
After selecting ok your "Combined" data will be sorted numerically based on the outline numbers to the right.
CodePudding user response：
Unfortunately the only fully general way to sort outline numbers (or more formally, "path-indexes") is with a custom comparison function. Unfortunately, the Excel sorting operations and functions do not support such a feature (not even from VBA). The Excel preferred way is with custom lists, but these are not workable for path-indexes.
This leaves two choices:
Do your sorting entirely in VBA: This works (I've done it) but is pretty involved and messy. Or,
Use a Helper Column with a VBA function: This works but is not fully general, because you have to know ahead of time what the maximum index values will be.
of these, #2 above is by far the simpler option, but it does have limitations (explained below).
Basically what we want if a VBA function that can take a string like "6.6.11.14" and make it always sortable in path index order. The problem with this string is that in text order two digit indexes like ".11" and ".14" come before ".2" rather than after it.
The obvious way to fix this is to fix this is to convert all indexes into 2-digit numbers with leading zeroes. So, 6.6.11.14 would become 06.06.11.14 and crucially 6.6.2.1 would become 06.06.02.01. Now these two path-index values will sort correctly use text sorting.
The catch, however, is that this is only true if each individual index number is never greater than two digits (99). Thus, 06.07.99 sorts correctly, but 06.07.110 does not under this scheme. This is easily fixable by simply raising it from two digits to three digits, but again, the catch is that you have to know this ahead of time.
So assuming that we do know ahead of time what the maximum size/(number of digits) will be for any single index number, we can use the following VBA function to reformat your outline numbers for a helper column:
Public Function OutlineSortingFormat(OutlineNumber As String, Digits As Integer) As String
Dim PathIndexes() As String
Dim Zeroes As String
Dim i As Integer
Zeroes = "0000000000"
PathIndexes = Split(NumberText, ".")
For i = 0 To UBound(PathIndexes)
PathIndexes(i) = Right(Zeroes & PathIndexes(i), Digits)
Next i
OutlineSortingFormat = Join(PathIndexes, ".")
End Function
This just splits the outline number into individual numeric strings, prefixes the correct amount of zeroes and then concatenates them back into a sortable outline number.
You then apply this by making a helper column and then using the function like so:
=OutlineNumbersSortedFormat(M3,2)
Where M is the column that has your unformatted outline indexes and the second parameter (, 2)) indicates that your want all index numbers filled (and truncated) to 2 digits. Then instead of sorting on your original outline numbers, your sort on the "helper column" containing the reformatted values.
