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

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.
--
David Biddulph

"mjones" wrote in message
ups.com...
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

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

On Aug 16, 2:25 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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.
--
David Biddulph

"mjones" wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -

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

On Aug 17, 12:12 am, mjones wrote:
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

On Aug 16, 2:25 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



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.
--
David Biddulph

"mjones" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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" wrote:

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

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


On Aug 16, 8:56 pm, bj wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -