That alt-0010 is used to force a new line within the cell.

If you're seeing a little box instead of seeing a new line, you could select
your cell(s) and do:

Format|Cells|Alignment tab|check the wrap text box.

If you really want to get rid of those alt-enters, here's a macro (saved and
modified from a previous post):


Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = chr(10)
AfterStr = " " 'space character???

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=BeforeStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With

End Sub

If you're using xl97, change that Replace( to application.substitute(

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Toby Stevenson wrote:

Hi Peo,

Yes I am sure they are carriage returns. =Find(CHAR(10)|G5) return a value
of 8. (| character is currently my list separator)

The problem with using alt 0010 was an artifact of using my laptop keyboard
which doesn't have a separate number pad, I hooked up an external keyboard
and that solved the problem, but lead to another one. I am now receiving an
error while trying to do this replace:

"Formula is too long"

Toby

"Peo Sjoblom" wrote:

Are you sure they are carriage returns?

=FIND(CHAR(10),A1)

or

=FIND(CHAR(13),A1)

if both formulas return an error then there must be something else

If you get a number then the replace must be done incorrectly make sure that
match entire cell contents is not checked under options and type either 010
or 0010
However, I have noticed sometimes that excel can't find a character that I
know is there and then if I close Excel, start again it will work

regards,

Peo Sjoblom

--

Dave Peterson