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

 

[David Biddulph]

My guess would be that your 2.1.0 and 6.6.1 are text entries, whereas the
other entries are numbers (such as 1.0 and 1.1) custom formatted the way you
describe. You can check by using the formulae =ISNUMBER(A1) and ISTEXT(A1),
copied down your colums as appropriate. The sort will put the numbers in
numerical order, then the text in alphanumeric order.

 

[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

 

[Guest]

what do the numbers look like when you format them as general.
check if you entered the 2.1.0 as 2.1. and the 6.6.1 as 6.6.1

the entry format needs to be 2.1 and 6.61

 

[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