Google Sheets – Split Function Converting Text to Date Before Splitting

google sheets

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.

Best Answer

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

Related Topic