Need help parsing ALT+ENTER entries from a single cell in MS Excel 2010

[BlackCat]

Greetings,<o></o>
<o> </o>
I'm exporting data from a Rational database application into an Excel 2010 spreadsheet. I need to parse the data in a way that will group the data by object ID (XX-YY-25xx). <o></o>
<o> </o>
https://www.box.com/s/vgq2hk2bq24ik7jjck9w<o></o>
<o> </o>
In the Sample Worksheet.xlsx file, on the Source sheet, I have a single Source column with multiple entries in each cell that are separately by what appears to be the ALT+ENTER character.<o></o>
<o> </o>
XX-YY-2516<o></o>
Nunc tincidunt commodo iaculis:<o></o>
Nam faucibus in nulla eu consectetur.<o></o>
Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<o></o>
Sed sagittis pulvinar lorem vitae euismod.<o></o>
XX-YY-2517<o></o>
Etiam eu commodo diam.<o></o>
<o> </o>
My goal is to parse out each of these entries as shown in the Output sheet, grouping them by object ID (XX-YY-25xx). I’m looking for a macro that will parse the data in the Source sheet so that it looks like the data in the Output sheet. Here are some additional notes:<o></o>
<o> </o>
1) I would like to have the macro ignore the first header row of the Source sheet, then perform its actions on how ever many rows are in the sheet, stopping when it reaches the end of populated rows (the actual output may range from a few dozen rows to hundreds of rows).<o></o>
2) The output can overwrite the data in the Source sheet, or be written to a new sheet in the workbook.<o></o>
3) Each grouped set of output data (Output sheet, ID column) will always begin with the “XX-YY-“ characters. When parsing through the source data (Source sheet, Source column) the macro should identify the first “XX-YY-“ entry, and then parse the data directly below until the next “XX-YY-“ entry is found, or the end of the cell is reached. <o></o>
<o> </o>
Can someone please provide me with a simple macro to do this?<o></o>
<o> </o>
Thanks in advance.<o></o>
<o> </o>
Paul<o></o>

 

[Andrew Poulsom]

Have you tried Data|Text To Columns? For the Delimiter put your cursor in the Other box, hold down Alt and type 010 on the numeric keypad.

 

[BlackCat]

Yes, I have tried Data|Text To Columns with the carriage return delimiter.

What I get is this (cell reference):

(B2) XX-YY-2516<O:P></O:P>
(C2) Nunc tincidunt commodo iaculis:<O:P></O:P>
(D2) Nam faucibus in nulla eu consectetur.<O:P></O:P>
(E2) Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<O:P></O:P>
(F2) Sed sagittis pulvinar lorem vitae euismod.<O:P></O:P>
(G2) XX-YY-2517<O:P></O:P>
(H2) Etiam eu commodo diam.<O:P></O:P>
<O:P></O:P>
As the Output sheet in the Sample Worksheet.xlsx file shows, what I want is this (cell reference):

(B2) XX-YY-2516<O:P></O:P>

(C2) Nunc tincidunt commodo iaculis:<O:P></O:P>
(C2) Nam faucibus in nulla eu consectetur.<O:P></O:P>
(C2) Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<O:P></O:P>
(C2) Sed sagittis pulvinar lorem vitae euismod.<O:P></O:P>

(B3) XX-YY-2517<O:P></O:P>

(C3) Etiam eu commodo diam.<O:P></O:P>
<O:P></O:P>
Thanks!

Paul

 

[Rick Rothstein]

Greetings,<o></o>
<o></o>
I'm exporting data from a Rational database application into an Excel 2010 spreadsheet. I need to parse the data in a way that will group the data by object ID (XX-YY-25xx). <o></o>
<o></o>
https://www.box.com/s/vgq2hk2bq24ik7jjck9w<o></o>
<o></o>
In the Sample Worksheet.xlsx file, on the Source sheet, I have a single Source column with multiple entries in each cell that are separately by what appears to be the ALT+ENTER character.<o></o>
<o></o>
XX-YY-2516<o></o>
Nunc tincidunt commodo iaculis:<o></o>
Nam faucibus in nulla eu consectetur.<o></o>
Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<o></o>
Sed sagittis pulvinar lorem vitae euismod.<o></o>
XX-YY-2517<o></o>
Etiam eu commodo diam.<o></o>
<o></o>
My goal is to parse out each of these entries as shown in the Output sheet, grouping them by object ID (XX-YY-25xx). I’m looking for a macro that will parse the data in the Source sheet so that it looks like the data in the Output sheet. Here are some additional notes:<o></o>
<o></o>
1) I would like to have the macro ignore the first header row of the Source sheet, then perform its actions on how ever many rows are in the sheet, stopping when it reaches the end of populated rows (the actual output may range from a few dozen rows to hundreds of rows).<o></o>
2) The output can overwrite the data in the Source sheet, or be written to a new sheet in the workbook.<o></o>
3) Each grouped set of output data (Output sheet, ID column) will always begin with the “XX-YY-“ characters. When parsing through the source data (Source sheet, Source column) the macro should identify the first “XX-YY-“ entry, and then parse the data directly below until the next “XX-YY-“ entry is found, or the end of the cell is reached. <o></o>
<o></o>
Can someone please provide me with a simple macro to do this?<o></o>

Give this macro a try...

Sub SplitSourceData()
  Dim X As Long, Z As Long, Index As Long, LineCount As Long
  Dim Lines() As String, vArrIn As Variant, vArrOut As Variant
  vArrIn = Sheets("Source").Range("A2:A" & Sheets("Source").Cells(Rows.Count, "A").End(xlUp).Row)
  For X = 1 To UBound(vArrIn)
    LineCount = LineCount + UBound(Split(vArrIn(X, 1), vbLf)) + 1
  Next
  ReDim vArrOut(1 To LineCount, 1 To 2)
  For X = 1 To UBound(vArrIn)
    Lines = Split(vArrIn(X, 1), vbLf)
    For Z = 0 To UBound(Lines)
      If Lines(Z) Like "[A-Z][A-Z]-[A-Z][A-Z]-####" Then
        Index = Index + 1
        vArrOut(Index, 1) = Lines(Z)
      Else
        If Len(vArrOut(Index, 2)) Then vArrOut(Index, 2) = vArrOut(Index, 2) & vbLf
        vArrOut(Index, 2) = vArrOut(Index, 2) & Lines(Z)
      End If
    Next
  Next
  With Sheets("Output")
    .Columns("A:B").Clear
    .Range("A1:B1") = Array("ID", "Description")
    .Range("A2:B" & UBound(vArrOut)) = vArrOut
    .Range("A1:B1").Interior.ColorIndex = 15
    With .Range("A1:B1,A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
      .Font.Bold = True
      .VerticalAlignment = xlTop
      .EntireRow.AutoFit
    End With
  End With
End Sub

 

[BlackCat]

Rick,

Your macro works perfectly.

Thanks!

PAul