Finding & replacing a hard return character

[Marcia]

I have exported data from Access into an Excel
Worksheet. All the hard returns within the Access cells
have been translated as little boxes at the end of the
text in the Excel cells.

I would like to remove all these little boxes from the
Excel file. When I try to search & replace them, Excel
does not the recognize box character. Any ideas?

 

[Gord Dibben]

Marcia

Select your range of data.

Edit>Replace

What: ALT + 0010(from the number pad keys)
With: enter a space or leave this blank

Replace All.

You may have to enter ALT + 0013 instead of 0010.

Note: when you type in the 0010 you won't see anything, but it is there.

Gord Dibben Excel MVP XL2002

 

[Dave Peterson]

I've never had real good luck using alt-0013 in the Replace dialog. (alt-0010
works fine, though.)

I've always used an extra helper cell and a worksheet formula:
=substitute(a1,char(13),"")

or a macro:
Option Explicit
Sub testme()
With worksheets("sheet1")
.Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


===
Char(10) in an excel cell is used for linewrap. If your box character is
char(10), maybe all you'll want to do is enable wordwrap.
(Format|Cells|Alignment Tab|WordWrap)

===
And to determine what the character really is:
=CODE(RIGHT(A1,1))
(since it's the last character.)

or a formula like:
=code(mid(a1,x,1))
(vary x = 1 to the len of a1)

And if you do this a lot, you'll want a copy of Chip Pearson's CellView addin.
http://www.cpearson.com/excel/CellView.htm

(In fact, it might be even better for you if you don't do it a lot!)