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.
KBear wrote:
I have an extract from a Lotus Notes Database. When entering data into the
database, we used the enter key to separate paragraphs. When this data is
placed into Excel, the carriage returns appear as either a BEL character of 7
or a DEL character of 127. Not quite sure, but it shows up as a square.
I would like to remove all these characters and replace them with a space.
If I use the Clean function, all of the characters (spaces) are removed, but
this does not leave any spaces so words and paragrahs then become combined.
I have tried a combination of the clean and substitute functions without any
success.
--
Dave Peterson