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

BlackCat

New Member
Joined
Jan 11, 2013
Messages
8
Greetings,<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
https://www.box.com/s/vgq2hk2bq24ik7jjck9w<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
XX-YY-2516<o:p></o:p>
Nunc tincidunt commodo iaculis:<o:p></o:p>
Nam faucibus in nulla eu consectetur.<o:p></o:p>
Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<o:p></o:p>
Sed sagittis pulvinar lorem vitae euismod.<o:p></o:p>
XX-YY-2517<o:p></o:p>
Etiam eu commodo diam.<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
2) The output can overwrite the data in the Source sheet, or be written to a new sheet in the workbook.<o:p></o:p>
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:p></o:p>
<o:p> </o:p>
Can someone please provide me with a simple macro to do this?<o:p></o:p>
<o:p> </o:p>
Thanks in advance.<o:p></o:p>
<o:p> </o:p>
Paul<o:p></o:p>
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jan 11, 2013
Messages
8
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
Greetings,<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
https://www.box.com/s/vgq2hk2bq24ik7jjck9w<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
XX-YY-2516<o:p></o:p>
Nunc tincidunt commodo iaculis:<o:p></o:p>
Nam faucibus in nulla eu consectetur.<o:p></o:p>
Cras aliquet fringilla augue, eget venenatis sapien euismod quis.<o:p></o:p>
Sed sagittis pulvinar lorem vitae euismod.<o:p></o:p>
XX-YY-2517<o:p></o:p>
Etiam eu commodo diam.<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
2) The output can overwrite the data in the Source sheet, or be written to a new sheet in the workbook.<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Can someone please provide me with a simple macro to do this?<o:p></o:p>
Give this macro a try...

Code:
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
 

Forum statistics

Threads
1,081,983
Messages
5,362,550
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top