Google-sheets – Can Google Sheets sort by “humanized” values

google sheets

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.

Best Answer

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".

Related Topic