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.

I have Sheet1 and Sheet2. With the data on either 1 or 2, when I run it, I get that error.

Could somebody use the Template I uploaded and attach a working Macro to it possibly? I'm good with Excel, just never used Macros before.

Hi All,

I have one queary ,In may collage 4Persons are got same marks as below.

Name University Markas Markas Plan
Nagaraju/Srinivas/Madu/Vijai Nu 25 30 40


I want to split these records as below .Is it possiable ,Please revert with code

Name University Markas Markas Plan
Nagaraju Nu 25 30 40
Srinivas Nu 25 30 40
Madu Nu 25 30 40
Vijai Nu 25 30 40

Thanks,

Nagaraju