Simple sort issue

E

Eloise

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!
 
L

Lt. Bonifacius

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.
 
Top