Ask Your Question
0

Sorting of numbers with slashes [closed]

asked 2013-05-13 08:23:04 +0100

Girish gravatar image

updated 2013-05-13 08:25:27 +0100

I want to sort the following numbers in a column. 2004/1, 2004/2, 2004/10, 2004/11, 2004/3, It should be sorted like this 2004/1, 2004/2, 2004/3, 2004/10, 2004/11

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-31 16:27:53.098557

3 Answers

Sort by » oldest newest most voted
0

answered 2013-05-13 09:36:10 +0100

Huskey gravatar image

Why not change the suffix' one digit to two, i.e. 2004/1 becomes 2004/01. Other numbers are 2004/02, 2004/03 ... 2004/10, 2004/11.

edit flag offensive delete link more
0

answered 2013-05-13 11:26:30 +0100

ROSt52 gravatar image

@JohnSUN - when I saw the @Girish's question I got the idea of formating the cells as a date yyyy/mm and then just sort. However this doesn't work.

Can you possibly give an explanation why it doesn't work?

The results I am getting are column A: descending sorting column F : ascending sorting both columns are formated like yyyy/mm

image description

edit flag offensive delete link more

Comments

@ROSt52 - You probably have dates with yyyy/m formatting, but original poster has strings. As strings list 10, 1, 101 is correctly sorted.

@Girish - I'd suggest converting these to dates, then you wouldn't have had a problem in the first place.

mahfiaz gravatar imagemahfiaz ( 2013-05-13 11:45:45 +0100 )edit

@mahfiaz - So it seems the strings are the issue. But what does "string" mean in this context? I checked Calc help but could not find the answer. Could you please continue to help?

ROSt52 gravatar imageROSt52 ( 2013-05-13 12:41:49 +0100 )edit

Your data in cell may contain many types of data, such as number, string (textual data), date, boolean (true or false). And of course you can have a text which may contain only numbers or "2013/01". Looks like a date, but technically it is not. Free tip of the day: If you want to explicitly enter string, start it with ' (apostrophe), this way it is not converted to number or date.

mahfiaz gravatar imagemahfiaz ( 2013-05-13 12:50:44 +0100 )edit
1

You can easily convert your data from a text representation of dates in the "true date" with a formula similar to =DATE(VALUE(LEFT(A2;4));VALUE(MID(A2;6;2));1)

JohnSUN gravatar imageJohnSUN ( 2013-05-14 09:10:13 +0100 )edit

@mahfiaz - "Looks like a date, but technically it is not"- I understand. I also understand now @johnSUNs "complicate" looking formula. His formula converts from text string to date. Thanks to both of you.

ROSt52 gravatar imageROSt52 ( 2013-05-15 05:06:19 +0100 )edit
0

answered 2013-05-13 10:03:00 +0100

JohnSUN gravatar image

Just add a supporting column, fill it with the formula

=VALUE(LEFT(A2;4))*100+VALUE(MID(A2;6;2))

and then sort the table by this values.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-05-13 08:23:04 +0100

Seen: 3,301 times

Last updated: May 13 '13