Help Sorting Outline Numbers

[mjones]

Hi All,

My spreadsheet has 9 columns where each row is an item. My items are
numbered in column A like this:

1.0.0
1.1.0
2.0.0
2.1.0
6.0.0
6.6.0
6.6.1

When I sort the data by column A, I get this incorrect sort:

1.0.0
1.1.0
2.0.0
6.0.0
6.6.0
2.1.0
6.6.1

The cells are formated as 0.0#.0#

I'm using the auto filter sort, but it comes out the same with a
regular sort.

I hope someone can help solve this problem.

Thanks,

Michele

 

[mjones]

Okay, well that's intereting. 2.1.0 and 6.6.1 are indeed NOT
numbers. Makes perfect sense why sorting normally doesn't work. Is
there a way to make this work 'cause I could really use this feature?

I'm thinking I'll have to put the different levels (between the
decimals) in different columns and do a three level search. I know I
can leave the first two 'levels' in the same column, but that might
confuse the users of the form, so three columns makes sense.

Thanks again if anyone knows,

Michele

 

[Pete_UK]

Why don't you just format those offending cells as you want them (use
the Format Painter from one of the other cells) and then re-enter the
numbers 210 and 661 to make the formatting take effect?

Hope this helps.

Pete

 

[mjones]

All your comments have helped me to sort this out. Thanks!

In general format, the cells look like:
1
1.01
2
2.01
2.02
2.021
6
6.06
6.061

When formatted as #0.00.00, they look like:

1.00.00
1.01.00
2.00.00
2.01.00
2.02.00
2.02.10
6.00.00
6.06.00
6.06.10

And, yes they sort properly.

Thanks so much everyone. Another successful remedy!

Michele