Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse cells with line breaks
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse cells with line breaks
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 jo wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse cells with line breaks
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? -- Rick (MVP - Excel) "jo" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse cells with line breaks
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? On Jan 16, 12:06*pm, "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "jo" wrote in message ... 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?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse cells with line breaks
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) "jo" wrote in message ... 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? On Jan 16, 12:06 pm, "Rick Rothstein" wrote: 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? -- Rick (MVP - Excel) "jo" wrote in message ... 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?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing line breaks into separate cells | Excel Discussion (Misc queries) | |||
how do I remove line breaks from several cells? | Excel Discussion (Misc queries) | |||
Line Breaks in cells - Mac OS X Leopard | Excel Discussion (Misc queries) | |||
Line breaks in my cells... | Excel Discussion (Misc queries) | |||
Programming Line Breaks into Cells | Excel Programming |