CODEWITHSUNDEEP
google-sheetssplit
Atlas7
Atlas7

Reputation: 2856

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

Say...

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 (as text) and cell 3 = 1995-12-31 (as text)

If I use the split function to do this (e.g. cell 2 = split(cell1,",")), it removes leading zero, and convert the yyyy-mm-dd text into a google date. I do not wish to have this conversion to take place. i.e. I just wanted straight and simple split a text, into columns of text (not numbers. not dates).

How do I do this out of the box? (I've tried google around but no luck). Is this even possible?

Side note: the "Data" => "Split text into columns" approach - no luck. It converts all numeric-like texts into numbers, and date-like texts into dates. I wish to have raw text throughout and no conversion like this to happen. How to do this?

Upvotes: 3

Views: 645

Answers (2)

futursimple
futursimple

Reputation: 49

My answer to essentially the same question: import your text-to-split as a text file into Sheets. During the import process you have the option to disable the automatic formatting.

Upvotes: 0

a-burge
a-burge

Reputation: 1574

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')

Upvotes: 2

Related Questions