Replace carriage return

[Dan]

Hello,
I have a file that contains carriage returns in some of the fields. I would
like to get rid of the carriage returns and replace them with a different
character of some sort (a pipe or back-slash). I have tried the replace
tool, but haven't had any luck with that -- even after researching the ASCII
codes to see if there was a code I could search by. The only other tool I
have gets rid of the carriage returns, but doesn't have an option to replace
them with a different character.

Any suggestions?

Thank you very much in advance,

Dan

 

[Dave Peterson]

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

 

[VBA Noob]

Thing this should work

Assumes text in cell A1

=(SUBSTITUTE(A1,CHAR(10),"/ "))

VBA Noob