When I use SPLIT function, the data in my cell gets converted from TEXT to Number (notice the preceding '0' in cell C3 is removed).
is there a way to retain the data as Text after the split (or at least retain the 0 in front) ?
=SPLIT( JOIN("!",B1:B), "!")
When entering a string that looks like a number, one can keep it as a string by preceding it with a single apostrophe '
(which does not become a part of the string). Same thing works in formulas:
=split(substitute("'" & B1, "!", "!'"), "!")
This appends ' at the beginning and after each separator (which has to be done before splitting). After splitting, the result is as desired: strings, no leading apostrophe.