Google Spreadsheet - How to split delimited text into columns without format conversions (i.e. preserve raw text)

Question

cell 1 contains this text: 03400561, 1995-12-31

I need a way to split this cell into 2 raw text columns". i.e.

My expected/wanted output: cell 2 = 03400561 and cell 3 = 1995-12-31 (both as text, not numbers nor dates)

If I use the split function to do this (cell 2 = split(cell1,",")), it removes leading zero, and convert the yyyy-mm-dd text into a google date.

No luck on the "Data" => "Split text into columns" approach. It converts all numeric-like texts into numbers, and date-like texts into dates. I wish to have raw text throughout and no conversion.

Answer 1

Ugly as hell, but seems to work on your example. Basically enclose the separator with double quotation marks to force sheets into interpreting the data as text. Then remove them and use arrayformula() to cover all the columns:

=arrayformula(substitute(SPLIT(char(34)&substitute(U19,",",char(34)&","&char(34))&char(34),",",true,false),char(34),""))

CHAR(34) evaluates to double quotation (i.e. ") to signify text entry. Just be aware that the second variable includes a leading space (' 1995-12-31')