Save As csv Carraige Return issue

[WaterBug]

I have a large xlsx file with the last column containing text with carraige
returns (CR). When I save this xlsx as a csv file these interim CR are
preserved so each record of data isn't continuous on a single line.

Example data:
f1, f2, f3, "data for field 4"
f1, f2, f3, "more data <carraige return>
for field 4"
f1, f2, f3, f4

I discovered this by opening the csv file in word with paragraph markings
turned on and I can see these paragraph marks.

Is this a 'feature' of Excel? How can I save a CSV without these pesky
returns? I am using this csv in an import routine on another program and it
fails without manual corrections.

Your help is appreciated...kelly

 

[Tom Hutchins]

Why not just remove the carriage returns before saving as a CSV file? I
adapted the following code from a reply Dave Peterson made to a similar
question today in the Excel Programming group:

Sub Remove_CR_LF()
'select any cell in the column to be processed before running macro
Dim myRng As Range
Set myRng = ActiveCell.EntireColumn
myRng.Replace What:=vbCr, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
myRng.Replace What:=vbLf, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Set myRng = Nothing
End Sub

Depending on your data, you may want to replace the vbCR or vbLF with a
space instead of an empty string.

Hope this helps,

Hutch

 

[WaterBug]

None of this is done with code. These questions pertain to a totally manual
process. Thanks for your help. Any idea why/how to not 'preserve' these cr
doing a manual save as?

 

[Tom Hutchins]

Okay, here is a non-VBA way to remove any carriage returns or line feeds.
Assuming the last column (the one with the problem text) is D, enter this
formula in E1:
=SUBSTITUTE(SUBSTITUTE(D1,CHAR(13)," "),CHAR(10)," ")
Copy this formula down through all rows of data. Recalc the worksheet. Copy
& paste column E in place as values. Delete column D. Column E becomes the
new column D; it has the same text, but without any carriage returns or line
feeds. Save As CSV file.

Hope this helps,

Hutch