I have a column with comma delimited values which look like this:

Thursday 9-10   
Thursday 7-8    
Thursday 8-9, Thursday 9-10
Thursday 8-9, Thursday 9-10, Thursday 7-8
...

(edited added one more example)

I wanted to get each of the values so I decided to use split like so:

=if(iserror(find(",",A1)), A1, split(A1, ","))

And it returns the following:

Thursday 9-10   
Thursday 7-8    
Tuesday 8-9 Saturday 9-10
Tuesday 8-9 Saturday 9-10   Friday 7-8

(edited added one more example)

It seems to me that the split function is not reading the cell as plain text (even though I formatted it to plain text) but as a date and then splitting on that basis.

Can someone explain to me why this is happening and how I can get it to ignore reading it as a date?

I have tried to concatenate the cell to a string with "" & A1 but that fails as well.

P.S. The first two values are returned as-is because there are no commas in those cells.

  • 1
    @Downvoter, may I know how I can improve my question? – Jeel Shah Nov 2 '16 at 18:04
up vote 2 down vote accepted

The easiest way to do this is with a regexreplace function, it effectively also preserves strings as strings by default, and its quite dynamic, making it one of my favorite functions:

=IFERROR(REGEXEXTRACT(A4,"("&REGEXREPLACE(A4,", ","), (")&")"),A4)

To Explain:

I start by building by own regex dynamically by placing the , (comma with the space) between to opposite facing parentheses, which partially create capture groups around the pieces of text I do want.

which starts to create a string that looks like this:

Thursday 8-9), (Thursday 9-10), (Thursday 7-8

To complete the capture groups, I start and end my string with the opening and closing parentheses like this:

="("&REGEXREPLACE(A4,", ","), (")&")")

which effectively would create a variable like this:

(Thursday 8-9), (Thursday 9-10), (Thursday 7-8)

By default a capture group automatically pushes each group to it's own cell, and since im not telling it what has to be in the parentheses, only that it should use the comma and space to know where the groups end and begin, i top it off with a regex extract function.

For the cases where there are no commas to replace, it would throw an error, so the easiest way is to wrap the whole thing with iferror so that it simply returns the original cell in those cases:

enter image description here

It is a hack but please try:

=split(t("'"&A1&char(160)),",")

The t to save bother with preparing the format for the cells and the non breaking space to force text (but invisibly).

  • This seems to fix the issue for the "Tuesday" value but it still returns a "Saturday" value. This the updated code: =if(iserror(find(",",A1)), A1, split(t("'"&A1&char(160)),",")) – Jeel Shah Nov 2 '16 at 17:39
  • Does it work with Thursday 8-9, Thursday 9-10, Thursday 7-8 ? There can be three time slots. Sorry, I should have clarified. – Jeel Shah Nov 2 '16 at 17:58
  • It works for two, just not three :P – Jeel Shah Nov 2 '16 at 18:02

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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