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é :

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?