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.

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.

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.

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.

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