Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
save as issue | Excel Discussion (Misc queries) | |||
File Save/Save As Issue | Setting up and Configuration of Excel | |||
VLookup column return issue | Excel Discussion (Misc queries) | |||
Save As Authentication Issue From ASP.NET | Excel Discussion (Misc queries) | |||
UserForm Save Issue | Excel Worksheet Functions |