carriage returns after import

[ken@work]

Hi all
I have imported a csv file that contains carriage returns
in one of the variable fields as well as text. How do i
get excel to find and delete/replace them or stop them
from being imported ?
i can see them in excel as the little sqares but cannot
search for them.
TIA
ken

 

[Gord Dibben]

Ken

Try Edit>Replace

what: ALT + 0010(on numpad)
with: space(or nothing)

OR ALT + 0013

Gord Dibben Excel MVP

 

[ken@work]

Gord, didn't work on the spreadsheet, said no cells found
with that symbol. Symbol for carriage return is more of a
rectangle. Any other suggestions ?
(couldn't get it to work on a win95 pc, but worked on
NT4.0)

 

[Gord Dibben]

Ken

In the replace what: box you held ALT key and typed 0010 on the numpad.

You will see nothing entered, but it is being entered.

If no go, download Chip Pearson's CELLVIEW add-in from

http://www.cpearson.com/excel/CellView.htm

Load through Tools>Add-ins.

Select the data and Chip's add-in will show you what the characters are.

The rectangles could be any one of a number of different characters.

David McRitchie's TRIMALL macro could also be of assistance.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Gord Dibben Excel MVP

 

[ken@work]

Thanks for that Gord, I loaded in the CELLVIEW add-in and
confirmed that the Carr. Ret. was 013. but was still
unable to find/replace the symbol. I can't see how the
trimall macro would help, (I prob jus don't understand it).
Is there a way to "turn on" special characters or a table
that shows them ?
Thanks, again.
Ken

 

[Gord Dibben]

Ken

You could try the CLEAN Function.

=CLEAN(cellref)

Alternate.....try Data>Text to Columns>Delimited by ALT + 0013 and see what
happens.

This will split your data into separate cells so make sure you have a backup
and a contingency plan.

To get a list of special characters enter =CHAR(ROW()) in row 1 of a column
then drag/copy down to row 255. You will see there are 31 rectangles.

Gord Dibben Excel MVP

 

[ken@work]

Yep thats what I ended up using, the CLEAN function. I
thought there may have been a way to type the special
characters that excel ignores.
Thanks for your help.

 

[Dave Peterson]

I've never been able to use the alt-0013 in the Edit|Replace dialog, either
(well, successfully <bg>).

The alt-0010 works ok, but you could use a macro to get the 0013 character. And
if you're using the macro for one, you could use it for multiple:


Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13))

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

Just keep adding those Hex codes you found using Chip's addin.

(I changed the funny characters to "". You could use " " if you wanted a
space.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm