How to remove or replace a carriage return character in a cell?

[Guest]

After importing a Cognos "hotfile" into excel, the cell contains a carriage
return character (looks like a square) and I want this to be a new line feed.
I have tried to determine how to do a find and replace, but no luck. Help!

 

[Dave Peterson]

Saved from a previous post:

You can use Chip Pearson's Cell View addin to find out the character it is:
http://www.cpearson.com/excel/CellView.htm

If those box characters are char(10)'s (alt-enters), you can use
edit|Replace
what: ctrl-j
with: (spacebar??)
replace all

If that box character is something else, you may need a macro:

Option Explicit
Sub cleanEmUp()

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

myBadChars = Array(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

 

[Guest]

Try this:

Edit one of the cells, select only the square character, copy it [Ctrl]+C ,
then press [Esc]

Edit>Replace
Find what: Paste in the character [Ctrl+V]
Replace with: Hold down the [Alt] key, type 010, release the [Alt] key.
Click [Replace All]


Does that help?