Hi There. I'm trying to automatically remove carriage returns from an Excel
2000 Spreadsheet. I've tried the holddown alt and type 0010 or 0013 in the
find and replace, but without success.
Any advice would be greatly appreciated.
Thanks
Hi There. I'm trying to automatically remove carriage returns from an Excel
2000 Spreadsheet. I've tried the holddown alt and type 0010 or 0013 in the
find and replace, but without success.
Any advice would be greatly appreciated.
Thanks
ASAP Utilities has a feature that does this nicely..........
Free at www.asap-utilities.com
Vaya con Dios,
Chuck, CABGx3
"Toby Stevenson" <Toby [email protected]> wrote in message
news:[email protected]...
> Hi There. I'm trying to automatically remove carriage returns from an
Excel
> 2000 Spreadsheet. I've tried the holddown alt and type 0010 or 0013 in the
> find and replace, but without success.
>
> Any advice would be greatly appreciated.
>
> Thanks
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
"Toby Stevenson" wrote:
> Hi There. I'm trying to automatically remove carriage returns from an Excel
> 2000 Spreadsheet. I've tried the holddown alt and type 0010 or 0013 in the
> find and replace, but without success.
>
> Any advice would be greatly appreciated.
>
> Thanks
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
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
CellView.zip is a free download from www.cpearson.com that actually SHOWS
you exactly what characters are in a cell, visible or not........it's really
good.
Vaya con Dios,
Chuck, CABGx3
"Toby Stevenson" <Toby [email protected]> wrote in message
news:[email protected]...
> 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
Cell View looks like a good program, and may be good for other applications, but not this one - it shows no CR or LF in the cell to delete
Problem still unsolved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks