Removing Carriage Return Symbols

[week18]

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.

 

[Asala42]

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

 

[Jay Petrulis]

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

 

[Chris Davison]

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),""))