=char(10) or hex-A or vbLf or chr(10) is the same as alt-enter (a linefeed).
=char(13) or hex-D or vbCr or chr(13) is the carriage return character.
So those two characters team up to form a carriage character/linefeed
combination. VBA has a constant for that, too: vbCrLf
I'd use some code like:
with worksheets("sheet9999")
.cells.replace what:=vbcrlf, _
Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
End with
Use
Replacement:=vblf, _
and format the cell/range to wraptext if you want to keep the alt-enter
functionality.
(You could also replace the vbcr character with "" to accomplish the same
thing.)
=========
This is my usual post for fixing these kinds of invalid characters--I didn't
post it because I didn't know what you really wanted.
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.aspx
Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.
In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.
You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.
Another alternative is to fix it via a formula:
=substitute(a1,char(##),"")
Replace ## 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(##)) '<--What showed up in CellView?
myGoodChars = Array("")
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:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)