I am trying to split this number that is in one cell of its own in I2: 0000320193-20-000096 However, when I use the formula '=split(I2, "-")', it gives me three cells with the following: "320193" , "20" , "96".

I need the zeroes behind each number to stay with each number, I just need the hyphens to be removed and each separate number to be in their own cells.

I have tried '=split(I2, "'-")'; however, this does not work either.

In addition, all the cells are formatted to be "plain text."

I can make it work if I use the "split by column" tool; however, it's tedious and removes the surrounding text to I2, which I need.

How can I change this formula to keep the zeroes in front of each number?

Thanks!

CodePudding user response：

Try this:

=ARRAYFORMULA(SUBSTITUTE(SPLIT("'"&I2&"'", "-"), "'", ""))

This will append apostrophe in the beginning and end of cell I2 to avoid auto formatting and since SPLIT return an array, we need ARRAYFORMULA to apply the SUBSTITUTE to all array element.

Output:

References:

• 

  CodePudding user response：

  Use regexextract(), like this:

  =regexextract(I2, "(\d )-(\d )-(\d )")