Ask a new question

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

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

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".

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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

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

•Beware of Scammers posting fake Support Numbers here.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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

Image

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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

Image

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

if with sql:

select * from basic_order_by_natural order by colIndex[0:0] COLLATE NATURAL_CMP;

https://b23.tv/ojqtPWF

Image

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Excel 365 Pro Plus with Power Pivot and Power Query.

Sort Dewey Decimal System.

Example as applied to a BOM to aggregate costs in a PivotTable.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 12, 2024 Views 118 Applies to: