How to remove carriage return

[Fabbe]

Hi,

I would like to remove carriage return (ALT + ENTER in a
cell) without using a macro.

Is that possible?

Thanks.


Fabbe

 

[Bernie Deitrick]

Fabbe,

=SUBSTITUTE(A1,"
"," ")

This formula won't copy and past well in all probability: after you
type in the first double quote, hit Alt-Enter, then finish typing the
rest of the formula. You didn't say what you wanted to replace the
Alt-Enter with, so I used a space in this example. If you do this in a
complete column, you can copy the formula down, then copy all the
formulas and paste values over the originals, then delete the
formulas.

HTH,
Bernie
MS Excel MVP

 

[Lady Layla]

Edit, Replace, enter ALT +ENTER in first box, nothing in second, Replace


: Hi,
:
: I would like to remove carriage return (ALT + ENTER in a
: cell) without using a macro.
:
: Is that possible?
:
: Thanks.
:
:
: Fabbe

 

[Bernie Deitrick]

Lady L,

That doesn't work, at least not in Excel 2000.

HTH,
Bernie
MS Excel MVP

 

[Peo Sjoblom]

Another way would be to run this macro

Sub Replace_Carriage_Return()
Selection.Replace What:=Chr(10), _
Replacement:=Chr(32), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


it also replaces the carriage return by a space,

if replaced with nothing

Sub Replace_Carriage_Return()
Selection.Replace What:=Chr(10), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

finally you can do this using edit>replace,
in the find what box hold down alt while typing 010 on the numpad,
release alt key and either put a space or leave the replace with box blank

 

[Chip Pearson]

Fabbe,

Open the Replace dialog from the Edit menu and in the Find What box, hold
down the ALT key and type 0010 on the numeric keypad. Leave the Replace
with box empty (or, enter a space if you want to replace with a space) and
click Replace All.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Gord Dibben]

Fabbe

Try Edit>Replace

What: Hold ALT key and type in 0010(or 0013) from the numpad(you won't see
anything but it is entered)

With: nothing or a space

Gord Dibben XL2002

 

[Lady Layla]

Works in 97 and XP and 2003 for me


: Lady L,
:
: That doesn't work, at least not in Excel 2000.
:
: HTH,
: Bernie
: MS Excel MVP
:
: : > Edit, Replace, enter ALT +ENTER in first box, nothing in second,
: Replace
: >
: >
: > : > : Hi,
: > :
: > : I would like to remove carriage return (ALT + ENTER in a
: > : cell) without using a macro.
: > :
: > : Is that possible?
: > :
: > : Thanks.
: > :
: > :
: > : Fabbe
: >
: >
:
: