Split cells with hard-coded line breaks

[Andrew Rossmann]

I occasionally get CSV or regular Excel files that have line-feed
characters in cells. This causes cells to have multiple lines in them
(you can also create them by using ALT-ENTER). I would like to know if
there is an easy way to break up these cells into individual rows,
without messing up the columns they are in.

As an example, if a cell has 3 rows, I want to basically create two new
rows beneath that, leave the first in the original cell, and put the
next two in the cells below.

There may be multiple columns with multi-line cells, but the number of
rows should match (it's basically a manufacturer/manufacturer-part-
number thing.)

 

[french-friend]

hello Andrew,

can you test this code :

Sub SplitLineBreaks()
Dim NumColInsert As Integer
On Error Resume Next

NumColInsert = UBound(split(Selection, Chr(10)))
Selection.Offset(0, 1).Resize(1, NumColInsert).Insert shift:=xlToRight
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Selection,
DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar
_
:="" & Chr(10) & ""

Rows(Selection.Row).AutoFit
End Sub

I have put an example here : http://cjoint.com/?hAmb0utHp1

regards



Il se trouve que Andrew Rossmann a formulé :

 

[Dave Peterson]

I think you have two problems.

One is the problem with the .csv files. I think that the only way you can fix
this problem is to fix it before you bring it into excel.

You could go back to the sender and ask them to use a different character (maybe
a |), then import that or you could fix the text file before you import into
excel.

I saved this from a previous post:

Option Explicit
Sub testme01()

Dim myFileName As Variant
Dim myContents As String
Dim FileNum As Long
Dim InQuotes As Boolean
Dim iCtr As Long

Dim FSO As Object
Dim myFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

myFileName = Application.GetOpenFilename("Text Files, *.txt")
If myFileName = False Then
Exit Sub
End If

Set myFile = FSO.OpenTextFile(myFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

InQuotes = False
For iCtr = 1 To Len(myContents)
If Mid(myContents, iCtr, 1) = Chr(34) Then
InQuotes = Not InQuotes
ElseIf InQuotes Then
If Mid(myContents, iCtr, 1) = vbLf Then
Mid(myContents, iCtr, 1) = "|" '<-------
End If
End If
Next iCtr

myFileName = Left(myFileName, Len(myFileName) - 4) & ".out"

Set myFile = FSO.CreateTextFile(myFileName, True)
myFile.Write myContents
myFile.Close

End Sub

You'll have to find a good character to use and then modify the code to use
that.

Then after you import the file into excel, you can select the cells and do:

Edit|replace
what: | (or whatever character you used)
with: ctrl-j (same as alt-enter to force a new line in the cell)
replace all

====================
The second problem is what to do with the excel file with alt-enters in various
fields.

Is there one column that has the trouble, or lots? What are the columns to look
at or do you want to look at all.


Do you just insert a new row with a value in that column and the rest of the
cells empty in that new row?

If it's multiple columns, how do you insert the new rows?

For instance, say you have a single(!) row with two columns that have this:

1 A
2 B

Do you end up with multiple(!) new rows that look like:

(four rows)
1 A
1 B
2 A
2 B

or just
(2 rows)
1 A
2 B

If it's the second, what happens if you don't have the same number of entries in
each field--do the last few rows leave that field empty?

 

[andyross]

It sort-of worked, but splits them into columns instead of rows. I
need it split into rows so that the columns remain aligned.

The two columns are a bit like this (with --- and | separating cells)
[I'm doing this through Google at work, so I don't have fixed-space
text]
---
MFPN |MFR
---
593D226X0035E2E3 |VISHAY
TPSE226M035R0200 |AVX
---
GRM1885C1H101JA01D |MURATA MANUFACTURING
C1608C0G1H101J |TDK
06035A101JAT |AVX
C0603C101J5GAC |KEMET
VJ0603A101JXA |VISHAY