0

For example, if the data is 2-columns:

Peter      11:00am San Francisco
Paul       6:00pm Santa Clara
Mary       1:00pm San Jose

Can Google Sheets sort it by the time so that it is 11am, 1pm, and then 6pm?

Right now it will sort it as 1pm, 11am, and then 6pm.

If the data is 3-columns, then the sorting by time is as wanted, but if the data actually is given to me as 2-columns, then it won't work. So it seems solutions can be (1) sort it by human values, or not as good: (2) have another column which takes column 2 and filter out the time and therefore containing only the time, and sorting by this third column. But the sheet will have a redundant column this way.

Improve this question

1 Answer 1

Reset to default
2

Google Sheets manipulates with data according to its formal structure. "11:00am San Francisco" is just a string of characters, and the only way it can sort strings is lexicographically.

You have to preprocess the data by splitting off the part representing time. But this does not require creating a new column in the spreadsheet. A "virtual column" can be created for this purpose, using array notation { }. Here is how: 

=array_constrain(sort({A2:B4, arrayformula(split(B2:B4, " "))}, 3, True), 10000000, 2)

Step by step:

  1. arrayformula(split(B2:B4, " ")) splits the column by space. Note that this assumes there is no space between "11:00" and "am".
  2. The array notation {A2:B4, arrayformula(split(B2:B4, " "))} means stacking the original and new columns side by side. Note that the time is the 3rd column of this new array.
  3. sort(..., 3, True) sorts by 3rd column, ascending
  4. array_constrain(..., 1e7, 2) is used to keep only the original two columns A and B. Its syntax also requires a limit on the number of rows. I put 1e7 there (10000000) because a Google spreadsheet can never have that many rows.

In the long run, your spreadsheet would benefit from a better data structure. If something is a time, it should be in a column for time; a city name should be in a column for city names, etc. Not two in one even if it "looks better".

Improve this answer
3
  • Google is doing a lot of things in AI and of course it can do humanized sort. Even some language that is kind of home brew like PHP has humanized sort. I can write it in many languages in 5 minutes. I bet some high school students and college students can write it in 10 to 15 minutes.
    – nonopolarity
    Commented Mar 17, 2018 at 20:42
  • @I'-'I Please post as an answer. I didn't know that, and many others probably don't know either. Comments aren't that visible.
    – user135384
    Commented Mar 17, 2018 at 21:36
  • 1
    @I'-'I The question says "But the sheet will have a redundant column this way". With the formula in my answer, or in your comment, the sheet will not have a redundant column.
    – user135384
    Commented Mar 17, 2018 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.