Results 1 to 3 of 3

Removing carriage returns from delimited file

This is a discussion on Removing carriage returns from delimited file within the Excel Questions forums, part of the Question Forums category; I have a comma delimited which uses double-quotes as the text qualifier. I import this file into another program and ...

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    US of A
    Posts
    444

    Default Removing carriage returns from delimited file

    I have a comma delimited which uses double-quotes as the text qualifier. I import this file into another program and perform some analysis before generating a report in Excel. The problem I've run into is one of the fields in the original file can have carriage returns/line feeds within the field. I need to remove those before I can analyze the file as it's causing problems during my import.

    I've set the variable "replaceVal" to the "Chr(13)Chr(10)" which I need to replace and that works but I need to make a few changes:
    I'm losing my "" around each field, is there any way around this? I need to resave the file in delimited format but the quotes around each field are not being retained.
    Can this be rewritten to replace only when the "replaceVal" is found within a string of ""?

    Code:
     
    Worksheets("Main").Columns("A:G").Replace _
    What:=replaceVal, Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=True

  2. #2
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,032

    Default Re: Removing carriage returns from delimited file

    I am not sure that I understand the problem that you are having with the double-quotes but, in case it helps, you can remove the Chr(13)Chr(10) using the Excel function CLEAN.
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,115

    Default Re: Removing carriage returns from delimited file

    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com