Text to column with line breaks

[b4davidd]

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

[p45cal]

I would use a fresh sheet for the results. In the code below I've cited Sheet3, so make sure that Sheet3 is empty before you try the macro. If you're happy with the result you could change the code to add a new sheet instead.

You should run the macro with the sheet with the original information being the active sheet.

I've added commented out code for possible changes too:
The line:
Set newcel = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
could be changed to:
Set newcel = .Cells(.Rows.Count, 1).End(xlUp).Offset(1 - (i = 0))
to put a space between each record.

Since you say you've 60 columns the line:
For j = 1 To 3
should be changed to:
For j = 1 To 59

Sub test()
Dim x, txt As String, i As Long, cel As Range, newcel 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))
With Sheets("Sheet3")
For i = 0 To UBound(x)
Set newcel = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
'Set newcel = .Cells(.Rows.Count, 1).End(xlUp).Offset(1 - (i = 0))
newcel.Value = x(i)
For j = 1 To 3 '..To 59 for your sheet
newcel.Offset(, j) = cel.Offset(, j)
Next j
Next i
End With
Next cel
End Sub

[b4davidd]

Good morning

A million thanks for your help, I cant believe that you “so easy” found a solution for the problem I have been working on for like a week now. However a small problem still exists, when I run the formula everything works fine at the beginning but then I suddenly get different errors:

If I run the code out of the excel sheet pushing the “play” bottom then I get an error stating

“400”

nothing else but the number 400.

If I run it out of the Visual basic window I get the error stating:

Run-time error ‘1004’:
Application-defined or object defined error


After I have posted this thread on this forum I have also been working with other people on the problem but the SAME error occured at the end. unfortunately i am not allowed to post the code yet since i have not posted enought threads... (see code on on OZ grid forum under - reformat layout of data)

Do you have any idea what the problem might be and how to solve it?

(Btw: the error occures after excel transferd 429 lines to the new spread sheet...)

So many thanks for your help

[b4davidd]

Good morning

I found the solution or cause of the problem. i had one cell in my database that had far too many information stored in it. once i delete it, the code runs perfect

thanks a lot for your help, really appreciate it!!!!

david