Saved from a previous post:
Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm
Since you do see a box, then you can either fix it via a helper cell or a macro:
=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")
Replace 13 with the ASCII value you see in Chip's addin.
Or you could use a macro (after using Chip's CellView addin):
Option Explicit
Sub cleanEmUp()
Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?
myGoodChars = Array(" "," ") '<--what's the new character?
If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
-------
Sometimes those funny characters don't work in the edit|Find dialog.
alt-0010 (or ctrl-j) (aka: alt-enters) work ok. char(13) has never worked for
me.
Some Dude wrote:
It's not carriage return. Know were I might find a table of all the other
symbols?
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
The little boxes could be carriage returns.
To get rid of then do an EditReplace
What: ALT + 0100(from the numpad, not the regular keys)
With: space or nothing
Replace all.
If your boss set his cells to "wrap text" he would not see the little
boxes.
If you want to see them, uncheck "wrap text"
Gord Dibben MS Excel MVP
On Tue, 27 Jun 2006 14:42:52 -0500, "Some Dude" wrote:
I imported some data from one an excel file on one of our company web
sites.
When I look at it everything looks fine. When my boss looks at it he sees
characters shaped like little boxes where I see blank spaces. I copied the
blank spaces and pasted them into Word and I can see the arrows you'd see
for tabs in a Word document if you selected View Paragraph marks.
How can I replace these with blank space in the Excel file?
Why can my boss see them in excel but I can't?
--
Dave Peterson