Quite tricky to phrase this question but I'm asking getting times ranges of when people are available in 3 hours chunks. They can answer with any combination of "00:00 to 03:00", "03:00 to 06:00", "06:00 to 09:00", "09:00 to 12:00", "12:00 to 15:00", "15:00 to 18:00", "18:00 to 21:00", "21:00 to 00:00", and "All Day". In my current "solution" I replace these strings as numbers, 1 through 8 with "All Day" just being an overwrite and check if all the numbers are consecutive and if so, concatenate two pre-determined strings depending on what's the first number and what's the last numbers.
If, for instance, someone replied with the following answer: "06:00 to 09:00, 12:00 to 15:00, 15:00 to 18:00, 18:00 to 21:00", how do I properly shrink that to "06:00 to 09:00, 12:00 to 21:00"? In my solution the formula just fails since they're not entirely consecutive. It will just return the original input string.
I think I probably need to address every answer as an array and somehow iterate through to see which values are consecutive, saving that to a separate array and then just replacing the text back to a readable format in another cell but I have no clue how I would go about comparing the first number to the second number without an obscene number of IF statements.
EDIT: I ended up coding a custom formula for Google Sheets in the script editor as I felt that was more readable
Best Answer
Given the examples in the post and in the sample spreadsheet (which are all in military time format of hh:mm), I used this in B1:
=ArrayFormula({"Condensed Answers";IF(A2:A="",,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH(A2:A,"All Day")),"All Day",A2:A)),"to 21:00, 21:00",""),"to 18:00, 18:00",""),"to 15:00, 15:00",""),"to 12:00, 12:00",""),"to 09:00, 09:00",""),"to 06:00, 06:00",""),"to 03:00, 03:00","")))})First, the header is set (which can, of course, be changed).
From there, locate the central line:
TRIM(IF(ISNUMBER(SEARCH(A2:A, "All Day")), "All Day", A2:A))Here, each element of A2:A is TRIMmed to be sure there are no superfluous spaces to start with. Then, if "All Day" is found anywhere, other times are omitted and just "All Day" is returned.
After that, it's just a series of SUBSTITUTEs working from the latest possible juxtaposed time to the earliest, replacing any existence of these with nothing.
Once all that is done, TRIM is used a final time to omit any resulting spacing that may have occurred from SUBSTITUTE calls.