Split a column interspersed with zeros into separate columns in Excel, while breaking at zeros

Question

I am looking to split a column array interspersed with zeros into separate columns in Excel while breaking at the zeros in the column and ignoring the zeros.

For example, if I have an array {1,2,3,0,4,5,0,0,6,7,0}, Excel should return three arrays - array 1 should be {1,2,3}, array 2 should be {4,5}, and array 3 should {6,7}. How can I do this in excel?

Answer 1

You need to use VBA for this.
How I would do it is:

• read every number that is numeric and not 0 into an array.
• count how many values the array has and read it into an integer:
  lets say i is the array count. Than divide it by 3. i = i/3
• check if i is a whole number.
  If yes, output the first i values of the array, then the second, and third.
• If it is not a whole number, example the array count was 13 => 13/3 = 4,333.
  Drop the fraction and check if it is even or odd.
  If it is even, the output will be 1. i+1 2. i+1 3. i of the array.
  If odd: 1. i+1 2. i 3. i

To get the first i, second i and third i part of the array, you need to use another integer ex. j. Set j to 0, create a do while array(j) = i-1 for the first part (-1 because the array index starts from 0, not 1).
The second part you set j = i(+1 if necessary), etc

Answer 2

I'd suggest two methods.. firsly with code (this is perhaps a more messy way of doing it than Divin3 suggested, but it's simpler for me to understand). Basically you create an array using "," as a delimiter, then you reassemble new arrays for non-zeros. When a zero is found, we save the current array variable and move to the next, to "assemble" the contiguous non-zero segments:

Sub splitter()
    Dim theNewArray(999)

    theStr = ActiveCell.Value
    theOldArray = Split(theStr, ",")
    x = 0

    theNewArray(x) = ""

    For i = 0 To UBound(theOldArray)
        If theOldArray(i) <> 0 And theOldArray(i) <> "0" Then
            If Len(theNewArray(x)) > 0 Then
                theNewArray(x) = theNewArray(x) & "," & theOldArray(i)
            Else
                theNewArray(x) = theOldArray(i)
            End If
        Else
            If Len(theNewArray(x)) > 0 Then
                x = x + 1
                theNewArray(x) = ""
            End If
        End If
    Next i

    For i = 0 To (x - 1)
        ActiveCell(1, (2 + i)).Value = theNewArray(i)
    Next i
End Sub

This will give you the following (where blue is the original data and orange is the output data):

Alternatively you might prefer to do this with a couple of simple functions... select your data and use 'text to columns':

Say your data is delimited. Select 'other' and enter 0 (zero), as shown below:

The rest can be left as default. You'll end up with the result shown below in yellow:

After that you can clean up the trailing comma characters using this formula:

=IF(F1=",","",IF(LEFT(F1, 1)=",",IF(RIGHT(F1, 1)=",",MID(F1, 2, (LEN(F1)-2)),MID(F1,2,(LEN(F1)-1))),MID(F1, 1, (LEN(F1)-1))))

The results of this formula is what you see above in orange.