Split comma delimited data but ignoring commas between brackets

[DeonM]

Hi

I have a set of data in the following format:

[TABLE="width: 1004"]
<tbody>[TR]
[TD]code[/TD]
[TD]other members[/TD]
[TD]modified[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Babalwa Lobishe (Economic Development, Tourism and Agriculture), Fikile Desi (Constituency Coordinator), Thembinkosi Mafana (Safety and Security), Nomamerika Magopeni (Sport, Recreation, Arts and Culture), Wandisile Jikeka (Corporate Services), Mbuyiseli Mkavu (Human Settlements), Andile Mfunda (Infrastructure, Engineering and Energy), Paticia Ndlovu (Public Health), Balu Naran (Budget and Treasury)[/TD]
[TD]2014-05-08 15:27:10[/TD]
[/TR]
</tbody>[/TABLE]

I need to split column B into separate rows and repeat the other data. The data is essentially comma delimited.

I have the following code that successfully splits Column B to new rows as needed:

Sub splitByColB()
    Dim r As Range, i As Long, ar
    Set r = Worksheets("Metropolitans").Range("B999999").End(xlUp)
    Do While r.Row > 1
        ar = Split(r.Value, ",")
        If UBound(ar) >= 0 Then r.Value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.EntireRow.Copy
            r.Offset(1).EntireRow.Insert
            r.Offset(1).Value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop
End Sub

My problem is that some of the data sometimes includes commas that appear between brackets (marked red in data above) that should be ignored as delimiters. I assume I should first replace all commas between brackets with something else and then reverse later, but have no idea how to do this.

Any help appreciated.

 

[Peter_SSs]

Hey Peter

Thanks for that. It works perfectly. I have been testing with about 200 rows too - and get similar time results. Your code is much, much faster. My total dataset has about 10 000 rows. Even though I only really have to run this procedure once to convert legacy data, the speed will be a big factor.

Much appreciated.

Cheers. I may have been wrong, but I got the impression earlier that because you had something that worked you hadn't tried the other options.


I also wasn't sure about your number of columns. Is it correct that you do have only 3 columns?

 

[DeonM]

Cheers. I may have been wrong, but I got the impression earlier that because you had something that worked you hadn't tried the other options.


I also wasn't sure about your number of columns. Is it correct that you do have only 3 columns?

My data actually has 4 columns, but your code handles the 4th column as well.

 

[Peter_SSs]

My data actually has 4 columns, but your code handles the 4th column as well.

Yes, provided you edit this 3 to 4.

    a = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 3).Value

 

[DeonM]

Yes, provided you edit this 3 to 4.

Thanks for pointing that out. It was filing the 4th column with values, but I wasn't noticing that they weren't correct. The dangers of cutting and pasting, but not really knowing what you're doing

 

[DeonM]

Yes, provided you edit this 3 to 4.

Hi Peter

I found another small issue in my conversion. In my original data, columns C:D look like this:
[TABLE="width: 328"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]created[/TD]
[TD]modified[/TD]
[/TR]
[TR]
[TD]0000-00-00 00:00:00[/TD]
[TD]2014-05-08 15:27:10[/TD]
[/TR]
[TR]
[TD]0000-00-00 00:00:00[/TD]
[TD]2014-04-30 09:51:24[/TD]
[/TR]
</tbody>[/TABLE]

(some rows may have actual time stamp values for created)

The date/time values however are formatted as General. Changing the formats on those cells to number or date etc. does not affect them. Therefore I assume they're basically stored as text in the original export. This is fine for me.

When I copy it with your code, column D ends up copying the date as a date value and the cell ends up being formatted as a custom format (yyyy/mm/dd h:mm).

My question is: can one modify your code to basically copy columns C and D as text (or perhaps values? - when I use .PasteSpecial xlPasteValues on these columns in other cases it pastes the text across without changing the values and cell formats)

tx

 

[Peter_SSs]

Does changing the last section of the code to this solve the problem?

    Next i
    With .Range("A2").Resize(k, 4)
      .Columns(3).Resize(, 2).NumberFormat = "@"
      .Value = b
    End With
  End With
End Sub

 

[DeonM]

Does changing the last section of the code to this solve the problem?

    Next i
    With .Range("A2").Resize(k, 4)
      .Columns(3).Resize(, 2).NumberFormat = "@"
      .Value = b
    End With
  End With
End Sub

Yup, that solves it. Thanks so much for the time and help. I can't thank you enough.

 

[Peter_SSs]

Thanks so much for the time and help. I can't thank you enough.

You just have.