split google sheet keeping the character

Question

In google sheets I've got the following result in cell A1 059gh|04mr6|059fz|059fw|033b7|059fv|05293|059fy|059fx|03e75|

I've used =transpose(split(A1,"|"))

but it gives me as a result 059gh 04mr6 059fz 059fw 033b7 059fv 5293 059fy 059fx 3E+75

How can I fix this ? (I want to keep 05293 and 03e75)

I've tried regex with no great success

Answer 1

In your situation, how about the following modification?

Modified formula:

=TRANSPOSE(SPLIT(SUBSTITUTE(A1,"|","|'"),"|"))

or

=TRANSPOSE(SPLIT(REGEXREPLACE(A1,"^|\|","|'"),"|"))

• It supposes that your sample value 059gh|04mr6|059fz|059fw|033b7|059fv|05293|059fy|059fx|03e75| is put into cell "A1".

• When a value of 059gh|04mr6|059fz|059fw|033b7|059fv|05293|059fy|059fx|03e75| is split by |, 05293 is used as a number value. It seems that this is the current specification. So, before each value is split, I added ' to the top character of each value. I thought that by this modification, 05293 might be able to be used as a string value.

Testing:

When this formula is used to your sample input value 059gh|04mr6|059fz|059fw|033b7|059fv|05293|059fy|059fx|03e75|, the following result is obtained.