Question about x.y.z order

  • Thread starter Thread starter sunxiangqian
  • Start date Start date
S

sunxiangqian

My god, I have some excel file, the data sheets have so many columns,
and I need order like following,

1.1.1
1.1.1.2
1.1.2.1
1.1.3
1.1.4.2.3
1.2.5
1.10.6
1.11.1

How ever, using the excel order function, it looks like
1.1.1
1.1.1.2
1.1.2.1
1.1.3
1.1.4.2.3
1.10.6
1.11.1
1.2.5

The 1.2.5 shell before 1.10.6, how can I do it?

Thanks for help.
 
If you entered the values as:

001.001.001.002
(as many leading 0's as you need for the biggest number)

Then your sort would work fine.

An alternative is to insert as many empty columns to the right and then using
data|text to columns|delimited by other (dot)
and then sort by those columns (multiple times)

Or recombine the separated fields into one field:
=text(b1,"000\.")&text(c1,"000\.")&text(d1,"000\.")&text(e1,"000")

Then sort by this column.
 
Back
Top