google-drivesorting
When I sort by title in Google Drive, I would like it to numerically go 1 → 2 → 3 … etc., but instead it goes 1 → 10 → 100 → 1000 → 1001 → 1002 … etc.
Is there any way to get it to do the first ordering? (Below is a screenshot of how it's ordered now.)
Note: I have Japan2013a (2).JPG, Japan2013a (3).JPG and so on also in the list.
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - "
& TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)
Here's a break-down description of the formula:
FILTER function will filter for all rows that have something.MONTH and DAY functions will extract the respective values
from the date.TEXT function will convert the value from point 2 into a pre-formatted STRING.ARRAYFORMULA function will apply all the above to the complete column (skipping the header).SORT function will sort the result, given by the
ARRAYFORMULA.The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.
See example file I've created: sorting dates as text
In the upper right, above your list of files, is a drop-down menu labeled "Sort". Your options are
Change to "Title" and you should be fine.
Best Answer
It has been indicated on the Google Product Forum that if you were to put leading 0's as placeholders it will sort properly.
So in @Gavin Hudson's example if you created files with names starting:
01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11
It would sort properly.
In the general case, you need to find out the order of magnitude of how many files you have overall (is it 10, 100, 1000, 10000, etc) - and use the number of zeros from that number at the start of the file-name. So in the example, because there are less than 100 files, you need the zero's from "10", ie only 1.
This is fine for manageable numbers of photos - but it could be very tedious for larger numbers.