CODEWITHSUNDEEP
google-sheets
jason
jason

Reputation: 4449

Google new spreadsheet Split() function bug?

try this.

in cell A1 10/8/2013 Mike1, 2013, 0

now Split(A1,",")

now change A1 and add a colon 10/8/2013 Mike:1, 2013, 0

Split(A1,",") ... notice how Mike:1 is gone. Bug?

I need to split something with a colon in it and it is chopping off part of the text

Upvotes: 0

Views: 169

Answers (2)

pnuts
pnuts

Reputation: 59485

I can't reproduce this issue, either with function SPLIT or with Split text into columns... and with or without formatting as dd/mm/yyyy hhhh:mm.

Select A1, Data > Split text into columns... is all that is required.

Upvotes: 0

Harold
Harold

Reputation: 3337

The problem is that "10/8/2013 Mike:1" is interpreted as a date, it seems to be something like: "dd/mm/yyyy hhhh:mm". (if you change the display of the first cell to raw text you'll see he is giving you a number: 41496).

try this formula: 

=arrayformula(SUBSTITUTE(arrayformula(split(REGEXREPLACE(A1;":";"#@#");","));"#@#";":"))

in a easier way: As I suppose you can't change the way it's displayed, the possible workaround that I see here is to first split around the ":", then split with the "," and then join the two first elements.

Upvotes: 1

Related Questions