Excel - Sort Numeric Data

[johngiggs]

I have an Excel spreadsheet that has a column with numeric data like the example below. I'd like to sort the numbers, but the default sort does not seem to sort them the way I'd like.

Data Before Sort:
25.12.9
25.12.21
25.12.11
25.12.12
25.12.2

Expected Results:
25.12.21
25.12.12
21.12.11
21.12.9
21.12.2

How do I go about sorting the output in that manner? Any help would be greatly appreciated.

Thanks,

John

 

[johngiggs]

The expected results should say:

Expected Results:
25.12.21
25.12.12
25.12.11
25.12.9
25.12.2

Thanks,

john

 

[xlbo]

It is because Excel is treating your last 2 digits as decimal and therefore .9 is greater than .11 or .12.

Only way I know around this is to create your own custom list. You can do this under Tools>Options>Custom Lists and they can be imported from a set of cells so if you have any data int he right order, this may be the way forward. Other than that, you will need to find a way to convince excel that you are not dealing with decimals or to use a helper column.eg.

With your data as posted in A1 to A5, enter the following in B1:

=VALUE(RIGHT(A1,LEN(A1)-6))

and copy down. This will take the integer value beyond the last . and put it in a cell on its own. You can then sort normally on this column to get your desired results

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

FIRST, you do NOT have NUMERIC DATA.

You have STRING DATA with NUMERIC and OTHER characters.

STRINGS and NUMERICS are STORED differently and consequently SORT differently.

To do what you want, your STRINGS need to be formatted diferently...
[tt]
25.12.09
25.12.21
25.12.11
25.12.12
25.12.02
[/tt]
Otherwise, you're stuck with some kludgy work-around like Geoff has suggested.

KNOW THY DATA!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

Fix the data and you'll never worry again.

Make sure you have a few blank columns to the right of your data (Insert some of necessary), select your column of values and do data /text to columns, Delimited, Other (Put a period in the box) and hit OK. You should now see your data split into each column of values delimited by the period.

Now just rejoin it all, but use the TEXT function to pad the values to 2 digits, eg assuming your data is now in A1:C100, in say D1 put

=TEXT(A1,"00")&"."&TEXT(B1,"00")&"."&TEXT(C1,"00")

and copy down. Then copy Col D, paste special as values and delete the other columns. Now sort to your hearts content.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------