Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
ASAP Utilities has a feature that does this nicely..........
Free at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "Toby Stevenson" <Toby wrote in message ... 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA for Excel 2000 file is corrupt | Excel Discussion (Misc queries) | |||
How can I prevent Excel 2000 from inserting hyperlinks? | Setting up and Configuration of Excel | |||
other systems detecting excel 4.0 if excel 2000 is installed | Setting up and Configuration of Excel | |||
pivotcell object to excel 2000 | Excel Discussion (Misc queries) | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |