Search code examples
splitgoogle-sheets

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?

Solution

  • Try entering a tick mark before each number:

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