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
0
Stevenson (2)
6/1/2005 8:52:10 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
273 Views

Similar Articles

[PageSpeed] 32

ASAP Utilities has a feature that does this nicely..........

Free at www.asap-utilities.com


Vaya con Dios,
Chuck, CABGx3



"Toby Stevenson" <Toby Stevenson@discussions.microsoft.com> wrote in message
news:6A580446-FF4E-45AD-8CBB-C12EF04DDAD6@microsoft.com...
> 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


0
croberts (1377)
6/1/2005 9:09:29 PM
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
0
PeoSjoblom (789)
6/1/2005 9:15:18 PM
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
0
Stevenson (2)
6/1/2005 9:22:02 PM
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
0
ec357201 (5290)
6/1/2005 11:13:07 PM
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 Stevenson@discussions.microsoft.com> wrote in message
news:FDF32156-F6D6-48EA-A0A9-3EEC5015B906@microsoft.com...
> 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


0
croberts (1377)
6/1/2005 11:25:42 PM
Reply:

Similar Artilces:

how do I make Excel default to General number format?
I have installed Excel 97 but the General cell format insists on formatting as the number format with two decimal places. Is the number you enter already a two decimal number? General removes any type of formating, and represents the contens as WhatYouSeeIsWhatYouGet "rjmo" wrote: > I have installed Excel 97 but the General cell format insists on formatting > as the number format with two decimal places. You may have accidentally changed the "Normal" Style. Go to Format | Style... and make sure "General" is applied to the "Normal" style. ...

Password excel 2007
How does one set up up a password for the excel file I have currentley have as a short cut' Thank you bill - Open the file (Office Button | Open ... ). To password-protect the workbook, choose Review (ribbon) | (Changes section) Protect Workbook. Enter a password. Click OK. Save the file. To encrypt the file using a password, choose Office Button | Prepare | Encrypt Document ... - Mike http://www.MikeMiddleton.com "bill" <bill@discussions.microsoft.com> wrote in message news:B14BA33B-26BF-4375-9A39-DD7E6435AC04@microsoft.com... How does one ...

Outlook 2000 professional Problem
Hi to all! I have a problem in Outlook 2000 professional installed in Windows 98 SE computer. When I add the Exchange mailbox I got an error The Name Could Not Be Resolved. The Operation Failed. However when I tried to installed Office XP in Windows 98 SE I can succesfully get the mailbox but the Office XP is just a beta version and we have a license only in Office 2000. Is there anybody encountered this problem using office 2000 professional. Thansk for the help! Emslan Check this out to see if everything looks OK. http://support.microsoft.com/kb/297801/en-us Nue "emslan"...

Opening Publisher 2000 file with Publisher 2003
I have been sent a file created in 2000, but use 2003, which says it cannot open earlier versions. Is there a download solution to this? -- Ghento Publisher 2003 will open 2000 documents. Some things to try: Disable your anti-virus software. Try opening the file in safe mode. Publisher 2000 and 2003 uses different WordArt, this could be a problem. Publisher 2003/2002 Add-in: WordArt Compatibility http://www.microsoft.com/downloads/details.aspx?FamilyId=897AA11B-A37D-4586-A1A7-54BBEA375AE2&displaylang=en How to troubleshoot a damaged publication in Publisher http://support.microsoft....

deleting duplicate files from Excell
Can anyone tell me how to delete multiple files in an Excell worksheet. It is a very large database with many multiple files and was wondering if there was a macro i could run Thank you Gloria at Habitat for Humanity You can select the whole lot, then do data>filter advanced filter, copy to another location (click a cell somewhere), and finally check unique records only -- Regards, Peo Sjoblom "Habitat for Humanity" <anonymous@discussions.microsoft.com> wrote in message news:0a9301c3a7b0$2acd82e0$a501280a@phx.gbl... > Can anyone tell me how to delete multiple...

How can I expose the actual column & row headers in excel?
We have a timesheet program an employee created for our company. She hid all the formulas and calculations. I have unprotected the sheet and am trying to adjust the code but the acutal headers are hidden and I cannot highlight them to unhide them. (ex: the actual column headers, ABCD, ets & the row headers, 123) Juanita Wrote: > We have a timesheet program an employee created for our company. She > hid all > the formulas and calculations. I have unprotected the sheet and am > trying to > adjust the code but the acutal headers are hidden and I cannot > highlight...

Excell defaults e-mai to expressl
In excel spreadsheet, the embedded e-mails are defaulting to Outlook Express which is not my e-mail server. Thank youi. What version of Outlook do you have installed? Is it set as your default email client? Do other email links (such as on web pages) bring up outlook? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRI...

Is Excel unreliable ?
Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ I don't understand what you mean by 'drag down the calculated cell' Also could we see the particular formula that Excel is getting wrong. I'm not aware that ...

Outlook 2000 to Outlook 2007
I currently use Outlook 2000 (through an exchange server), but have purchased a new computer which will be used in a different office and has Outlook 2007 installed. I want to copy all of my contacts and emails to the new computer. What do I do ? Unless you're using a Personal Folder for storage, you need do nothing, the default storage location in an Exchange Server environment is your Exchange Server mailbox; Outlook 2007 will find everything there. If you are using a ..PST, look here: http://www.slipstick.com/config/backup.htm Outlook & Exchange/Windows Messaging Backup and Dua...

MS Excel 2000 Cell Capacity and Data Display
I'm working with a large Excel 2000 spreadsheet in which most of the cell entries are text. Some text cell entries exceed 300 words. I'm using the wrap text and row/column autosizing features. All of the text displays correctly when I'm in the cell or in the formula box. When I move to anyother cell, however, the text in the cells is cropped. How do I get all of the text to display and print properly? Thanks. If you add alt-enters every 80-100 characters, you can see lots of characters. You may have to manually adjust the rowheight, though. RCM-IEWTD wrote: > &g...

Difference between basing pivot on excel list or other pivot table
What is the difference between basing the pivot table on excel list of other pivot table report? thanks Hi Gio The big pro for basing off another Pivot Table is the reservation of memory. The big con is that both are linked. This means much of what you do to one, effects the other. Perphaps the one that most get caught with is the 'grouping' of data. Group one Pivot Table and any others based off it will also be grouped. I will often base one PT off another to save the user from having to drag fields about. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins ...

How can I show values, not formulas in Excel?
When I add a VLOOKUP function and fill down, the formula, not the resultant value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in not checked. Thanks! Could be that your cells are formatted as text, format as General. HTH JG "jwbass" wrote: > When I add a VLOOKUP function and fill down, the formula, not the resultant > value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in > not checked. > > Thanks! Make sure it is not formatted as text -- mphell0 -------------------------------------------------------------...

how do i use the excel symbol toolbar?
Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the easy insertion of commonly used symbols. The problem is, I can't figure out how to use it. I have tried dragging the symbols from the toolbar onto a worksheet, copying them, double clicking them, etc... Nothing works. Does anyone have any idea how to do this? It's not even in Excel Help. Select the symbol and click insert button Regards, Peo Sjoblom "Gwhizkids" wrote: > Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the > easy insertion of commonly used sy...

Date returned Incorrect
I got this code on google the owner could be Brian S or FlyGuy I have modified to suit but I am getting an incorrect date returned. If I search for 2 folder dates on the same Drive I get the correct answers for both folders, if I search for folders on different Drives I get the correct date on the first folder, and the incorrect date on the second folder of 02/01/1601 22:00:00 which is most likely the prime first date. Is there a work around for this? I have tried to clear the Vars but this did not work. Thanks On Sun, 25 Jul 2010 09:35:21 -0700, LondonLad <LondonLad@...

Excel 2003 math error
I have a cell that evaluates to 318.43. When I multiply that cell by 156, Excel gives me 49674.51, but the correct result, when I do the math by hand (and by Google), is 49675.08. What the ___ is going on? Hi Try extending the number of places of decimal on the first cell, and you will probably see that it is not 318.43 but something smaller (318.4263) which has been rounded to 318.43 With your evaluation formula that results in a displayed value of 318.43 in say C1, try =ROUND(C1,2)*156 -- Regards Roger Govier <cyraxote@gmail.com> wrote in message news:8801150f-5557-412d-9143-9...

Outlook 2000 warning message when moving items to folder
We have a user who receives this warning when moving items from one Outlook 2000 folder to another existing folder (i.e. - selecting multiple deleted items and moving all items to Personal folder): "Creating a new item from the selected items could take some time. Are you sure you want to create a new item from these 62 items?" Of course the amount changes depending on how many folder items he's selected. Thanks for any help you can give. Are the two folders of different types? In other words, is one a mail folder and one a task folder? If so, when you drag an item fro...

Outlook 2000: spam blocks retrieving messages
I've got about 20 users here using Outlook 2000 SP3 on either Windows 2000 SP4 or Windows XP SP2 (with all the latest patches to both the operating system and Office). We've got Symantec Antivirus 9.0.1 and are using SpyBot or Ad-Aware for anti-spyware (depends on the computer), and everything here checks out clean, so whatever's going is not being caused by those factors. Here's what happens. We have a POP3 server here (Eudora Internet Mail Server running on Mac OS X 10.3.7). I'm not currently doing any kind of spam-blocking but am looking into options because some of our...

bug in Excel?
Hi! I created a worksheet which is linked to two different "source" workbooks to consolidate data. I am using this workbook for several consolidations (same format and i just save the changed data under a file "source 1" and "source 2" for then automatically obtaining the sums in my CONSOLIDATION workbook) and that works fine. But after consolidating different workbooks and obtaining 3 different CONSOLIDATED files (which i save under another name after consolidation) for year1, year2 and year3, the values in those files change as soon as i open another one!...

Excel document properties insert into a cell
I would like to find a way to insert the data that is stored on the "Properties" page of an excel document into the cell. In work and Visio I can do it easily but I can't figure out how to do it in Excel. I know, it's probably simple, but I just can't think that simple right now... thanks mark Mark, You will need a UDF Function DocProps(prop As String) application.volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties _ (prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function and enter in a cell suc...

How do I enter a decimal in a string of numbers in Excel? Example.
Please post your question in the BODY of the message, not the subject line, and clarify with an example of the initial data and what you want to end up with. If it's to translate 123 to 1.23, you divide by 100. On Wed, 9 Feb 2005 15:03:07 -0800, Joy <Joy@discussions.microsoft.com> wrote: ...

Having trouble printing selected sheets in Excel
I am trying to print my document, and I select the content. I go to the File, and select "Print Area", then I select "set print area". I go into the print preview, and the selected pages are at 21 %. I adjust it back to 100 %, then select ok. I look at the document, and the same page is repeated in the print preview. I go back through the process, of selecting print area, setting the print area, and print preview, and the pages are at 21 %. I have tried right clicking, and resetting all page breaks, clearing the print area. I have also closed the document with saving ...

How Change default "look in" location in EXCEL for Importing Exte.
I am importing data contained in a text file. When I get to Import Data, the "Look In" folder location always defaults to "My Data Sources". How do I change that to the folder where my data is? (files are on my computer). thanks ...

Creating thousands of hyperlinks in Excel
I'm creating a huge directory and I'd like to include internal hyperlinks. Is there a FAST way to do this? i.e. I need to create 2000 hyperlinks, leading from one cell to a cell on another worksheet. Tips? Thanks! I'd use the =hyperlink() worksheet function. David McRitchie posted this and it might help you: =HYPERLINK("#"&CELL("address",C5),C5) =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5) =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) Megan wrote: > >...

excel formula needed
I need a formula to 1) look in colunm a, gather any numbers present in that colunm, even though most wil be empty 2) then i want them to take that list, get rid of the empty cells and put that data either into a new table or on another sheet. thanks. Sounds like a sort would work. If you want a function, try this: =IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL(IF(A1:A20<>"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"") Enter with Ctrl+Shift+Enter, not just enter... HTH, Ryan--- -- Ryan--- If this information was helpful, please indica...

Too many Excel applications running at the same time
I have MS Excel 2010 installed on my computer. Every time I open up an Excel file it opens a new Excel application in my Windows dock at the bottom of the screen. If I open 5 Excel docs I have 5 separate Excel applications running. Before I had Excel 2010 installed I would only have one Excel app. running and I would see teh separate files in the Switch Window command at the top of the screen. What actions do I need to take so that I don't have numerous Excel apps running, instead of only one? I doubt if you have multiple Excel applications open. What you probably have is a button for e...