Parse cells with line breaks

[jo]

I have a spreadheet with multiple columns of data and some with line
break characters within the cells.

I have an identifier in the left column, A, and the cells with line
breaks inside to the right. For example,

ID Books

1 The Hobbit
Driving Miss Daisy
2 Only One book

3

4 First Book for id 4
Second book for id 4
Third book for id 4





I need to move this information to another sheet such that the ID is
repeated for each item in the books column. The number of rows per ID
would equal the number of line breaks + 1. How can I do this?

 

[excel-ant]

Hi Jo,

You can use the following Macro to fill down any empty cells with the
value immediately above
==========================================
Sub FillDown()

Dim cl As Range

For Each cl In Selection
If cl = "" Then cl.Value = cl.Offset(-1, 0).Value
Next

End Sub
===========================================
Anthony
http://www.excel-ant.co.uk

 

[Rick Rothstein]

Your question is not entirely clear to me; perhaps it is just due to the way
the data got posted. Consider ID 2... is there a line break in that cell
after the one book title? Or does that blank line represent an empty row
between ID 2 and ID 3? (Notice there is no blank line between ID 1 and ID 2,
hence my confusion.)

Also, how you want to show your data on the other sheet is not entirely
clear to me either. Do you want it laid out like this?

ID Books
1 The Hobbit
1 Driving Miss Daisy
2 Only One book
3
4 First Book for id 4
4 Second book for id 4
4 Third book for id 4

Where each of the lines above is in its own row (in 2 columns per row)? Did
you want ID 3 (the one with no book title) carried across like I show, or
was it not supposed to be shown? If the answer to my first question is that
ID 2 has a line break in it, did you want the blank line after the only
title in it shown or not?

 

[jo]

Thanks for the response. In my example, there are four rows. Row
three, book column is blank.

Your output shown below is exactly how I would like my output. Based
on my data, there should only be one row with id 2. There should be
one row with id 3 but with no book name.

Does this help?

 

[Rick Rothstein]

I'm pretty sure this macro will do what you want (change the worksheet names
in the two Set statements to your actual source and destination worksheet
names)...

Sub ParseBookNames()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim DestRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim Text() As String
Set Source = Worksheets("Sheet1")
Set Destination = Worksheets("Sheet2")
DestRow = 1
With Source
.Rows(1).Copy Destination.Cells(DestRow, "A")
DestRow = DestRow + 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow ' Assumes Row 1 is header row
If IsNumeric(.Cells(X, "A").Value) And _
.Cells(X, "A").Value <> "" Then
Text = Split(.Cells(X, "B").Value, vbLf)
If UBound(Text) >= 0 Then
For Z = 0 To UBound(Text)
Destination.Cells(DestRow, "A").Value = .Cells(X, "A").Value
Destination.Cells(DestRow, "B").Value = Text(Z)
DestRow = DestRow + 1
Next
Else
Destination.Cells(DestRow, "A").Value = .Cells(X, "A").Value
DestRow = DestRow + 1
End If
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


Thanks for the response. In my example, there are four rows. Row
three, book column is blank.

Your output shown below is exactly how I would like my output. Based
on my data, there should only be one row with id 2. There should be
one row with id 3 but with no book name.

Does this help?