I'm sure there's an easy fix for this one, but I don't see any previous posts
about it, so here goes...

I have a long (about 500 lines) list of numbers that are sorting 10s, 11s,
etc. with 1s. How do I get the higher numbers lower down the list?

Here's what I mean. The following are sorting like this when I use auto
filter:

1.1.1.2.1.2
1.1.1.2.2.11.8
1.1.1.2.2.6.9
1.1.1.2.3.4
1.10.1.2.1.1
1.10.2.3.2
1.12.2.2.3
1.14.5.2.6
1.2.1.6.4.1
1.2.1.7.2.4.1

when they should sort like this:
1.1.1.2.1.2
1.1.1.2.2.6.9
1.1.1.2.2.11.8
1.1.1.2.3.4
1.2.1.6.4.1
1.2.1.7.2.4.1
1.10.1.2.1.1
1.10.2.3.2
1.12.2.2.3
1.14.5.2.6

thanks!

Hi Eloise!

The problem is that Excel is sorting the information like text not like
numbers. You have to use the Text-To-Columns function in order to separate
the information and then sorting.

1. Select the data you want to transform i.e. the column where the series is.
2. Then go to the "Data" menu then clic on "Text to columns..."
3. Make sure Delimited is selected, then clic next.
4. In Step 2 of 3 in Delimiters select the option "Other" and in the box
write "." (This is telling Excel that the information between "." should be
separated).
5. You can see in the data preview how would your data look like.
6. You can clic next or either go to the next step and select a data type
for each column.
7. Once your information is separated in columns select all the information
or cell in the information and go to to the "Data" menu the go to "Filter"
and clic "Automatic Filter".
8. Select each arrow from left to right and clic in "sort ascending".
Note. The data in the cells must be formatted as numbers.

Hope it works for you.

Eloise

Those are not "numbers", but text so they sort as text.

See this google search for a couple of methods of sorting data like this.

http://tinyurl.com/2e2g2r


Gord Dibben MS Excel MVP


On Wed, 2 Apr 2008 12:41:03 -0700, Eloise
wrote:

I'm sure there's an easy fix for this one, but I don't see any previous posts
about it, so here goes...

I have a long (about 500 lines) list of numbers that are sorting 10s, 11s,
etc. with 1s. How do I get the higher numbers lower down the list?

Here's what I mean. The following are sorting like this when I use auto
filter:

1.1.1.2.1.2
1.1.1.2.2.11.8
1.1.1.2.2.6.9
1.1.1.2.3.4
1.10.1.2.1.1
1.10.2.3.2
1.12.2.2.3
1.14.5.2.6
1.2.1.6.4.1
1.2.1.7.2.4.1

when they should sort like this:
1.1.1.2.1.2
1.1.1.2.2.6.9
1.1.1.2.2.11.8
1.1.1.2.3.4
1.2.1.6.4.1
1.2.1.7.2.4.1
1.10.1.2.1.1
1.10.2.3.2
1.12.2.2.3
1.14.5.2.6

thanks!