b4davidd
10-10-2007, 11:29 PM
Hey Guys
I have a problem with coping text to column in a vertical way (text to row) so that everything else on the same row is also copied to all the new rows the new cells have been created to.
Actually there is two problems involved, because the original cell has more then one line (lines are separated with Alt+Enter) and Somehow Excel does only notice the first line when I use text to column. (This problem has been solved with the formula stated bellow)
Let me show you how my original spread sheet looks like and how I want it to look at the end…
Cell A1: (After the ";" is an ENTER)
Bookrunner: Lehman Brothers;
Mandated Arranger: CapSource Financial Inc;
Participant: Citibank NA
Cell B1: USA
Cell C1: 100
Cell D1: Investment grade
And I want it to be like this at the end:
Cell A1:
Bookrunner: Lehman Brothers
Cell B1: USA
Cell C1: 100
Cell D1: Investment grade
Cell A2:
Mandated Arranger: CapSource Financial Inc
Cell B2: USA
Cell C2: 100
Cell D2: Investment grade
Cell A3:
Participant: Citibank NA
Cell B3: USA
Cell C3: 100
Cell D3: Investment grade
With the code
Sub test() Dim x, txt As String, i As Long, cel As Range For Each cel In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) txt = cel.Value If Right(txt, 1) = Chr$(10) Then txt = Left(txt, Len(txt) - 1) x = Split(txt, Chr(10)) For i = 0 To UBound(x) Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = x(i) Next Next cel End Sub
I can solve the first problem, that I can to “text to row” when cell has more then one line (line break with ENTER). But then, the information on the right sight of the original cell does not automatically copy itself as shown in the example above.
(The excel file has like 2000 rows and 60 columns!)
Thank you very much for your help
I have a problem with coping text to column in a vertical way (text to row) so that everything else on the same row is also copied to all the new rows the new cells have been created to.
Actually there is two problems involved, because the original cell has more then one line (lines are separated with Alt+Enter) and Somehow Excel does only notice the first line when I use text to column. (This problem has been solved with the formula stated bellow)
Let me show you how my original spread sheet looks like and how I want it to look at the end…
Cell A1: (After the ";" is an ENTER)
Bookrunner: Lehman Brothers;
Mandated Arranger: CapSource Financial Inc;
Participant: Citibank NA
Cell B1: USA
Cell C1: 100
Cell D1: Investment grade
And I want it to be like this at the end:
Cell A1:
Bookrunner: Lehman Brothers
Cell B1: USA
Cell C1: 100
Cell D1: Investment grade
Cell A2:
Mandated Arranger: CapSource Financial Inc
Cell B2: USA
Cell C2: 100
Cell D2: Investment grade
Cell A3:
Participant: Citibank NA
Cell B3: USA
Cell C3: 100
Cell D3: Investment grade
With the code
Sub test() Dim x, txt As String, i As Long, cel As Range For Each cel In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) txt = cel.Value If Right(txt, 1) = Chr$(10) Then txt = Left(txt, Len(txt) - 1) x = Split(txt, Chr(10)) For i = 0 To UBound(x) Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = x(i) Next Next cel End Sub
I can solve the first problem, that I can to “text to row” when cell has more then one line (line break with ENTER). But then, the information on the right sight of the original cell does not automatically copy itself as shown in the example above.
(The excel file has like 2000 rows and 60 columns!)
Thank you very much for your help