+ Reply to Thread
Results 1 to 7 of 7

Removing Carriage Returns in Excel 2000

  1. #1
    Toby Stevenson
    Guest

    Removing Carriage Returns in Excel 2000

    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

  2. #2
    CLR
    Guest

    Re: Removing Carriage Returns in Excel 2000

    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




  3. #3
    Peo Sjoblom
    Guest

    RE: Removing Carriage Returns in Excel 2000

    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


  4. #4
    Toby Stevenson
    Guest

    RE: Removing Carriage Returns in Excel 2000

    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


  5. #5
    Dave Peterson
    Guest

    Re: Removing Carriage Returns in Excel 2000

    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

  6. #6
    CLR
    Guest

    Re: Removing Carriage Returns in Excel 2000

    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




  7. #7
    Registered User
    Join Date
    05-24-2011
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: Removing Carriage Returns in Excel 2000

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1