I'm having troubles sorting numbers. I have a large spreadsheet with numbers with decimals. It sorts like this: 11.99 110.01 - instead of going from 11.99 to 12.00. Also 10.34 to 100.01, instead of 10.34 - 11.00. Won't numbers sort consecutively?
iMac, OS X Yosemite (10.10.4), iPad, iPhone 6, Macbook
Please post a screenshot or copy some of the data from the table and paste it here
One reason Wayne may be suggesting you post a screenshot is that it sound from your description that you may have text in your cells rather than numbers. Numbers naturally align to the right in the cells, whereas text may align left by default.
Sorted by column A (which contains text):
Sorted by column B (which contains numbers):
If you have text instead of numbers one possibility reason is that your region settings use , as the Decimal separator.
SG
Here are several screen shots from my spreadsheet, which totals over 4500 entries. The numbers are for a photography database for our historical society. Each donor receives a number. I would like the numbers to go consecutively. My Language & Region area on system preferences look just like posted above. So I don't think that's the issue. And I have made sure that column is numbers and not text.
So when you select a cell and look at Data Format in the format pane at the right you see something like this:
Or this:
And NOT this?
SG
But Automatic does not break down as yours.
Can you change it to 'Number'?
SG
So here's the snag. I need 4 decimals for some of my very large donor numbers. But when I change it to Numbers, I have to increase the number of decimals, from 2 decimals to 4 decimals, so it screws up my number system. See first screen shot.
Here's how it works with 3 decimals, but #4 needs 3 decimals. And # 174 needs 4 decimals. So if I change it to 4 decimals, those needing only 2 decimals automatically increase to 4, which gives them a completely different number. By the way, I tried exporting to Excel, and it also numbered the way numbers did, and because I hate Excel, I couldn't find a way there to number consecutively. If it would work there, I could export back to Numbers.
So Automatic works but doesn't sort consecutively.
I also tried the Custom, but that didn't work either. I didn't know how to "Add a Rule."
I'm not sure I follow you, but it seems that you have a "numbering system" here and don't need actual numbers that you will sum, etc. If so, I suggest using Text format with leading zeros.
Both of these columns, for example, will sort the way you would expect.
If the digits after the decimal are significant to the sorting then "pad with leading zeros" there too, e.g. 0010.034, 0010.099, etc.
If you are entering the "numbers" by hand simply type and apostrophe (single quote or ') and then the "number" and Numbers will know you are entering text rather than a real number.
SG
are you saying that you want "11.10" to be sorted differently than "11.100"??
how would you expect these to sort?
can you share the general form of the ID?
Yes, this is just a numbering system, not for any additional info other than to classify a photo according to its given number.
Yes, those are two different numbers for our photos: 11.10 is a different photo from 11.100. I would like 11.99 to be followed by 11.100.
Hello
If I understand it correctly, you should use text format for your numbering system because it is actually coding system and generate separate sort key from the code.
Something like this.
Table 1 A1 code (text) A2 10.01 A3 10.010 A4 10.99 A5 10.100 A6 11.00 A7 11.10 A8 11.99 A9 11.100 A10 12.00 A11 111.01 B1 sort key B2 =VALUE(LEFT(A2,FIND(".",A2)-1))*1000000+VALUE(RIGHT(A2,LEN(A2)-FIND(".",A2))) B3 =VALUE(LEFT(A3,FIND(".",A3)-1))*1000000+VALUE(RIGHT(A3,LEN(A3)-FIND(".",A3))) B4 =VALUE(LEFT(A4,FIND(".",A4)-1))*1000000+VALUE(RIGHT(A4,LEN(A4)-FIND(".",A4))) B5 =VALUE(LEFT(A5,FIND(".",A5)-1))*1000000+VALUE(RIGHT(A5,LEN(A5)-FIND(".",A5))) B6 =VALUE(LEFT(A6,FIND(".",A6)-1))*1000000+VALUE(RIGHT(A6,LEN(A6)-FIND(".",A6))) B7 =VALUE(LEFT(A7,FIND(".",A7)-1))*1000000+VALUE(RIGHT(A7,LEN(A7)-FIND(".",A7))) B8 =VALUE(LEFT(A8,FIND(".",A8)-1))*1000000+VALUE(RIGHT(A8,LEN(A8)-FIND(".",A8))) B9 =VALUE(LEFT(A9,FIND(".",A9)-1))*1000000+VALUE(RIGHT(A9,LEN(A9)-FIND(".",A9))) B10 =VALUE(LEFT(A10,FIND(".",A10)-1))*1000000+VALUE(RIGHT(A10,LEN(A10)-FIND(".",A10))) B11 =VALUE(LEFT(A11,FIND(".",A11)-1))*1000000+VALUE(RIGHT(A11,LEN(A11)-FIND(".",A11))) C1 rand C2 =RAND() C3 =RAND() C4 =RAND() C5 =RAND() C6 =RAND() C7 =RAND() C8 =RAND() C9 =RAND() C10 =RAND() C11 =RAND()
Notes.
Formula in B2 can be filled down.
Column C is for testing purpose.
Table is built with Numbers v2.
Hope this may help.
H
what is the general format for the IDs? how many digits are allow before the decimal and after?
Hi Grand'
"(T)his is just a numbering system, not for any additional info other than to classify a photo according to its given number."
"11.10 is a different photo from 11.100. I would like 11.99 to be followed by 11.100."
Your described use implies that part of the number identifies a "class" or group to which a particular photo belongs. My assumption is that the first part of the number identifies this "class" (eg. nature, action, architecture, etc. or photographer, or camera). The second part appears to be a serial number assigned to each photo in this class.
You want a sort that sorts all of the photos by their category (the number to the left of the dot), then sorts within each category by the serial number part (the number to the right of the dot). There are two ways to go about this:
Hiroto's "sorting key" column (in column B of his table) takes the second approach.
The first part of his formula— VALUE(LEFT(A2,FIND(".",A2)-1))*1000000— pulls out the characters to the left of the ".", interprets them as a number, then multiplies that number by 1000000 to ensure that none of the original digits will be changed by adding the result of the second part.
The second part—VALUE(RIGHT(A2,LEN(A2)-FIND(".",A2)))— pulls out the digits to the right of the "." and interprets them as a number.
The two numbers are then added to produce a single number. That number will always start with the digits that are to the left of the "." in the original ID. Those digits will always be followed by six more, ending with the digits that were to the right of the "." in the original ID.
The full number will sort in the order you are looking for.
(The random numbers in Hiroto's column C provide a means of re-sorting the table into a random order. As Hiroto states, that column is there for testing purposes; It would not be included in your table.)
The approach I numbered "1" uses a pair of formulas, each essentially the same as part of Hiroto's method:
C2 and filled down: =VALUE(LEFT(B,FIND(".",B)-1))
D2 and filled down: =VALUE(RIGHT(B,LEN(B)-FIND(".",B)))
Sorting the table, first on column D, then on column C, gives the results shown below:
Note that the text values "11.00" and "12.00" in column B drop the second zero when converted to numbers in column D. As your example shows no values that end ".00", I've assumed these do not occur in your list, and the dropped digit will cause no problems.
"I also tried the Custom, but that didn't work either. I didn't know how to "Add a Rule.""
"Add a rule" in the instance displayed will either allow you how to display "special" values such as zero or numbers less than zero, or will permit you to add conditional formatting rules that compare the value in the cell with another value and apply colour fill or colour text depending on the result of the comparison. Unlikely to be of any help in the issue under discussion.
Regards,
Barry
I'm having troubles sorting numbers.
