Hi rdy,
A 'custom format' for these 'numbers' will be very difficult.
When more than one decimal point is entered into a string of digits, the string immediately ceases to represent a "number" and becomes a text string. Text strings sort alphabetically, not numerically, so the task becomes one of creating a set of hierarchical numbers or a set of text strings that will sort as a set of hierarchical 'numbers'.
An hierarchical numbers set is probably easier, but requires some cut and paste and use of an hierarchical sorting algorithm.
Creating the set would be done in a word processing or text editing application where the version numbers were listed one to a line, then had the dots (periods) replaced by tabs, using Find/Replace.
With the dots replaced by tabs, the list could then be copied, then pasted into a spreadsheet where it could be sorted in as many stages as there are sections in the longest of the 'numbers.
Assuming 4 (eg 2.12.1.1 ), pasted into columns A-D, that would require an ascending sort on column A, then a sub sort within each group (same first-section number) on column B, a second sub sort on column C and a final sub sort on column D.
These sorts would require that all of the substrings ( 2, 12, 1 and 1 ) were recognized as numbers and sorted as numbers.
Doable, but much trouble if done often.
My choice was to attempt a set of formulas that would:
- count the numbers of dots in each version number
- find the location of each dot, and the length (in characters) of the version number
- use this information to deconstruct the version number into its components
- pad each component string with leading zeroes to make each the same length
- rejoin the 'standardized' components into a single sortable string that would sort in the same order alphbetically as it would numerically.
With the original 'number' and the constructed string both on the same row, sorting one column would rearrange the rows, resulting in the 'numbers' also being sorted.
Example: The first image is of the table in random order. Version numbers are in column A, sortable strings in column M. (all other columns may be hidden).
Second image is of the same table, now sorted ascending on column M:
Columns B-G are used as indexes in extracting characters from the string in column A.
All formulas are entered in Row 2 of the named column, then filled down to the last row of that column.
B2: LEN(A2)−LEN(SUBSTITUTE(A2,".",""))
SUBSTITUTE replaces the dots in the string with null strings, a text value with zero LENgth, then 'counts' the dots by subtracting the length of the altered string from that of the original string.
C2: In the example, these zeroes are directly entered. Replacing the 0 with a formula, =0, would make the value automatically fill into added rows. 0 is the position of a 'virtual' dot before the version number.
D2: IF($B2<COLUMN()−3,$G2,FIND(".",$A2,C2+1))
If the number of dots in the version number is less than column - 3 , all dots have been located, so get the position of the 'virtual' dot after the string from column G. If not, then find the next dot, starting one character after the previous one.
This formula is filled right to column F, then all three are filled down to the bottom row.
G2: LEN($A2)+1
Locates the 'virtual' dot following the version number.
Column H was left empty as a visual indicator of the end of the first stage and beginning of the next.
Columns I, J, K, and L extract the digits from each section, attach them to three leading zeroes, then place the three rightmost characters of the resulting string into their cell.
I2: IF(LEN($A2)<1,"",RIGHT("000"&MID($A2,C2+1,MAX(0,D2−1−C2)),3))
The IF part checks for a version number in this row of column A, and if there is none, places a null string in its cell.
If there is an entry in column A, the second part of the formula starts at the first character after the dot location in C2, and returns the number of characters determined by subtracting 1 and the dot location in C2 from the next dot location in D2. If that result is negative (as it would be if C2 and D2 contained the same number) the formula returns zero characters.
The formula is filled right to L2, then all four are filled down to the last row of the table.
M2: I2&J2&K2&L2
This uses the concatenation operator ( & ) to join the three digit strings in columns A, J, K and L into a single 12 digit string that will sort the same alphabetically into correct 'numerical' order.
Thanks for the question. It was an interesting challenge, which led me up a few blind alleys.
Regards,
Barry
