Save As csv Carraige Return issue
 

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

Save As csv Carraige Return issue
 

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" wrote:

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

Save As csv Carraige Return issue
 

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" wrote:

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" wrote:

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

Save As csv Carraige Return issue
 

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

"WaterBug" wrote:

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" wrote:

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" wrote:

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