Removing Carriage Return Symbols

week18 · Apr 29th, 2002, 01:52 PM

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 · Apr 29th, 2002, 02:07 PM

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 · Apr 29th, 2002, 02:12 PM

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 · Apr 30th, 2002, 11:59 AM

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

Apr 30th, 2002, 11:59 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 01:52 PM	#1
week18 New Member Join Date: Apr 2002 Posts: 1	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.

Apr 29th, 2002, 02:07 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	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

Apr 29th, 2002, 02:12 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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