Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Removing Carriage Return Symbols

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When importing text that was created in a DOS-based data collection package, small rectangular boxes appear where the carriage return was struck during the original data capture. I've tried copying the small boxes to the clipboard to remove them globally, but they do not copy to the clipboard. Any suggestions how they can be removed? Thank you.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    For some odd reason this seems to work more or less consistently:

    Select the column and go to DataText to ColumnsFinish - no more boxes. If that fails, you might want to try a TAB delimiter - but usually this works (for me anyway) w/o one.

    Adam

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Try,

    Sub ReplaceCR()
    Cells.Replace What:=Chr(10), Replacement:=""
    End Sub

    I think there are other codes which equal carriage returns, but I don't know them off hand.

    Bye,
    Jay

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you can also clean those cells using =CLEAN

    so =CLEAN(A1) and copy down.... from the help file it says "Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed."

    it doesn't, however, clean character # 127, whatever that is. So substitute it first...

    =CLEAN(SUBSTITUTE(A1,CHAR(127),""))
    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

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
  •