CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
SoWhat
SoWhat

Reputation: 5622

Split an a comma seperated list of number like strings as text

I have a column which contains comma seperated numbers

A1: 004,005,0005,00005

I want to split and more stuff with this. When I split I end up with the following. Losing the zeros because excel parses them as text

=split(A1, "," )

4 | 5 | 5 | 5 instead of

004 | 005 | 0005 | 00005

The number of zeros is important. I will pass on the result to get a vertical list

=unique(transpose(arrayformula(trim(split(join(",",!A1:A),",")))))

Upvotes: 1

Views: 767

Answers (3)

JvdV
JvdV

Reputation: 75840

With just a single value in A1 you can use a little trick I described over at "Web-Applications" in this cross-website post:

=SPLIT(SUBSTITUTE("'"&A1,"#","#'"),"#")

The single quote will force GS to format the returned elements as being text. We can use this principle inside an arrayformula if you have to concatenate multiple strings. You don't need ARRAYFORMULA() per se, but instead of JOIN() you'd need TEXTJOIN() to use the 2nd parameter and exclude empty cells from being joined.

enter image description here

Formula in B1:

=UNIQUE(TRANSPOSE(SPLIT(SUBSTITUTE("'"&TEXTJOIN(",",TRUE,A1:A),",","#'"),"#")))

Upvotes: 1

Jason E.
Jason E.

Reputation: 1221

Adding a single quote before a number will keep the format as is, thus, keeping the leading and trailing zeroes. Please see formula below:

=SPLIT(SUBSTITUTE(","&A1,",","#'"),"#")

enter image description here

Upvotes: 2

Erik Tyler
Erik Tyler

Reputation: 9345

Try this:

=ArrayFormula(REGEXREPLACE(SPLIT(REGEXREPLACE(A1,"(\d+)","~$1"),","),"~",""))

What this formula does is first replace every group of numbers with a tilde (~) and that same group of numbers. When SPLIT then acts on this new configuration, splitting at the commas, every group of numbers has the tilde in front of it and so retains all digits (because it is seen as a string and not a number). Finally, the outer REGEXREPLACE just gets rid of the tildes.

Upvotes: 1

Related Questions