How to sort {9.2.7, 7.0.10, 10.0.10} kind of values in excel

Question

How to sort {9.2.7, 7.0.10, 10.0.10} kind of values in excel

Answer 1

You can easily complicate the whole thing:

Index	Header	Order
7.0.10	a	1
7.2.1	d	2
7.2	c	3
7.10.0	e	4
9.2.7	f	5
10.0.10	g	6
11.2.3.5	j	7
11.2.3.4.7	i	8
11.2.3.4	h	9
7.0.10.1	b	10

Sort all data based on index column. This is the correct result:

Index	Header	Order
7.0.10	a	1
7.0.10.1	b	10
7.2	c	3
7.2.1	d	2
7.10.0	e	4
9.2.7	f	5
10.0.10	g	6
11.2.3.4	h	9
11.2.3.4.7	i	8
11.2.3.5	j	7

The point is that there is no simple workaround, we must split the Index by . to get the numbers then we can sort the data by that numbers. However you do it.

Andreas.

Answer 2

A Python function can sort it more directly, but it's limited (AFAIK) to only 3 levels. (as per your example data)

Answer 3

Hi. I would prefer to do it without helper columns.

Answer 4

Use same length for each number. Then you can sort it.

=TEXTJOIN(".",,TEXT(TEXTSPLIT(A2,"."),"00"))

Answer 5

You have to split your text to number parts, e.g.:

.

Now you can sort all data, by a,b,c and get the desired result. There are several ways to perform the split, I suggest to copy column A to B, then use "Text to Columns". https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Andreas.