CODEWITHSUNDEEP
splitgoogle-sheets
JBird
JBird

Reputation: 211

split() in google sheets changes to numeric

I've a cell in a google sheet with the following: =transpose(split( "01 02 03"," "). I expect, because it's text that it will keep the leading zeros (0). However, I end up with cells that are obviously numeric as it has removed the leading zero: <1> <2> <3>

In fact, this problem occurs at the level of each individual value. If I use instead =transpose(split( "01 02A 03"," ") I get <1> <02a> <3>.

Now, before you ask, YES the cells are formatted as "plain text".

There are two questions, the first one rhetorical: WHY google?

The second real question: Is there a way to force split() to keep the leading zeros? or alternatively Can I prevent sheets from treating the output of split() as potentially numerical?

Upvotes: 1

Views: 793

Answers (3)

futursimple
futursimple

Reputation: 49

Sheets allows you to circumvent the automatic formatting when you import your text-to-split as a text file:

Checkbox to enable/disable automatic formatting during import

Upvotes: 0

pnuts
pnuts

Reputation: 59485

An alternative without function or formula would be to apply to 01 02 03 Data > Split text to columns... with Space as Separator and then Copy and Transpose its output.

Upvotes: 0

Ed Nelson
Ed Nelson

Reputation: 10259

Try entering a tick mark before each number:

=transpose(split( "'01 '02 '03"," "))

Upvotes: 2

Related Questions