Thread: Excel Split Single Cell Array into Multiple Cells

This will do it of a single cell:

Sub x()

Dim v, w, i As Long

v = Split(Range("A1"), ";")

For i = LBound(v) To UBound(v)
    If Len(v(i)) > 0 Then
        w = Split(v(i), "=")
        Range("A1").Offset(1, i + 1) = Trim(Replace(w(0), """", ""))
        Range("A1").Offset(2, i + 1).Resize(3) = Trim(w(1))
    End If
Next i

End Sub

This is a better representation:

    ----------------------------------------------------------------
    |   |  data0  |  data1  |  data2 |  data3  |  data4  |  data5  |
    ----------------------------------------------------------------
    | 1 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
    ----------------------------------------------------------------
    | 2 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
    ----------------------------------------------------------------
    | 3 |  ARRAY  |  value1 | value2 |  value3 |  value4 |  value5 |
    ----------------------------------------------------------------
    . . . . and so on.

This is a sample of the data:

"search-terms1" = Chinese jade; "search-terms2" = Chinese archers; "search-terms3" = Antique jade; "search-terms4" = Asian jade; "search-terms5" = China history;

If possible, an equation would work better for this situation, but a macro could work, if it could do this to all rows in the database.

Please address Richard's post.

Are the Value items numeric, alpha or a mixture? Numbers and letters. No symbols as far as I know.

How many cells are involved? There are hundred of rows, and there will be a result of 5 columns per row. search-terms1 - search-terms5.

And in which case how do you want the second and subsequent cells parsed. I'm looking for the result to be 5 new columns, each with the data from the array.

Do other cells have exactly the same 'data1', 'data2' items or do they have further unique values like 'data6', 'data7' etc which extends the table further to the right. Just data1 - 5, which will actually be called search-terms1 - 5

Here is a database sample.

Results on sheet2:

Sub x()

Dim v, w, i As Long, r As Range

For Each r In Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
    With Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
        .Value = r
        .Offset(, 1).Value = r.Offset(, 1)
        v = Split(r.Offset(, 1), ";")
        For i = LBound(v) To UBound(v)
            If Len(v(i)) > 0 Then
                w = Split(v(i), "=")
                .Offset(, i + 2) = Trim(Replace(w(1), """", ""))
            End If
        Next i
    End With
Next r

End Sub

Could you embed that into a excel document or something for me? I get an error when I try to run it.

I'm getting "Object required" for this line:

With Sheet2.Range("A" & Rows.Count).End(xlUp)(2)

Nope.

I just made a new Macro, pasted this in:

Sub x()

Dim v, w, i As Long, r As Range

For Each r In Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
    With Sheet2.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
        .Value = r
        .Offset(, 1).Value = r.Offset(, 1)
        v = Split(r.Offset(, 1), ";")
        For i = LBound(v) To UBound(v) - 1
            If Len(v(i)) > 0 Then
                w = Split(v(i), "=")
                .Offset(, i + 2) = Trim(Replace(w(1), """", ""))
            End If
        Next i
    End With
Next r

End Sub

... and tried to run it. Did I forget something? Do I need to select something before running? Runtime error 424.