Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I automatically remove carriage returns in Excel?
I have an Excel spreadsheet saved from nn Access database that had carriage
returns in it. How can I now automatically remove these from Excel? Search-and-replace does not work. Thank you, Mike O. |
#2
|
|||
|
|||
Find hold down alt and type 010 on the numpad, replace with space or nothing
should work or run a simple macro like Sub Clean_Carriage_Return() Selection.Replace What:=Chr(10), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub Regards, Peo Sjoblom "Mike O." wrote: I have an Excel spreadsheet saved from nn Access database that had carriage returns in it. How can I now automatically remove these from Excel? Search-and-replace does not work. Thank you, Mike O. |
#3
|
|||
|
|||
Mike
What are you searching for? Usually CR's are CHAR 10 or CHAR 13 EditReplace what: ALT + 0010 or 0013 from the NumPad with: nothing or a space Hold the ALT key and type 0010 on the NumPad(at right side of keyboard) You won't see anything in the box, but it is there. If this doesn't bring you joy, post back. There are other methods. Gord Dibben Excel MVP On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike wrote: I have an Excel spreadsheet saved from nn Access database that had carriage returns in it. How can I now automatically remove these from Excel? Search-and-replace does not work. Thank you, Mike O. |
#4
|
|||
|
|||
I have tried all of these steps and nothing seems to work. I am doing a
copy/paste from outlook 2003 to excel 2003. The street line with more than one line sends all the data after to the next line and if there are 3 lines it gets bumped yet again. "Gord Dibben" wrote: Mike What are you searching for? Usually CR's are CHAR 10 or CHAR 13 EditReplace what: ALT + 0010 or 0013 from the NumPad with: nothing or a space Hold the ALT key and type 0010 on the NumPad(at right side of keyboard) You won't see anything in the box, but it is there. If this doesn't bring you joy, post back. There are other methods. Gord Dibben Excel MVP On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike wrote: I have an Excel spreadsheet saved from nn Access database that had carriage returns in it. How can I now automatically remove these from Excel? Search-and-replace does not work. Thank you, Mike O. |
#5
|
|||
|
|||
I've never gotten Char(13) to behave nicely in the Edit|Replace dialog.
Saved from a previous post: Chip Pearson has an addin that can help you find out what is exactly in that cell. http://www.cpearson.com/excel/CellView.htm If it turns out to be "nice", you can use Edit|Replace what: alt-xxxx (use the numbers on the number keypad--not above the QWERTY keys) with: (spacebar) or whatever you want. This can work nicely with alt-enters (alt-0010), but will fail with other characters (alt-0013 for example). You could use a macro to clean them up: Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim iCtr As Long myBadChars = Array(Chr(yy), Chr(zz)) For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), Replacement:=" ", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next iCtr End Sub Change the yy/zz to what Chip shows (and you can drop ", chr(zz)" if you only have one offending character). (And I changed them to space characters.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm joe smith wrote: I have tried all of these steps and nothing seems to work. I am doing a copy/paste from outlook 2003 to excel 2003. The street line with more than one line sends all the data after to the next line and if there are 3 lines it gets bumped yet again. "Gord Dibben" wrote: Mike What are you searching for? Usually CR's are CHAR 10 or CHAR 13 EditReplace what: ALT + 0010 or 0013 from the NumPad with: nothing or a space Hold the ALT key and type 0010 on the NumPad(at right side of keyboard) You won't see anything in the box, but it is there. If this doesn't bring you joy, post back. There are other methods. Gord Dibben Excel MVP On Mon, 24 Jan 2005 12:11:03 -0800, "Mike O." <Mike wrote: I have an Excel spreadsheet saved from nn Access database that had carriage returns in it. How can I now automatically remove these from Excel? Search-and-replace does not work. Thank you, Mike O. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I get carriage returns to not be row delimiters in text fi. | Excel Discussion (Misc queries) | |||
Carriage Return in Excel | Excel Discussion (Misc queries) | |||
Using Jet to read excel file returns blank for last cell - sometim | Excel Discussion (Misc queries) | |||
getting data from 2 excel sheets automatically | Excel Worksheet Functions |