removing char(13) with find/replace

[BorisS]

I have found that char(10) is the equivalent of ctrl-j in the find/replace
box. Is there an equivalent way of finding char(13)?

Thanks.

 

[Dave Peterson]

I can't get anything to work manually.

So I use a macro:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(13))

myGoodChars = Array(" ") '<--what's the new character, "" for nothing?

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

=======
If you're working with lots of those funny characters, you may want to get Chip
Pearson's addin:
http://www.cpearson.com/excel/CellView.htm

Then if you have lots of characters to fix, you can loop through them:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

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