How to remove carriage return

  • Thread starter Thread starter Fabbe
  • Start date Start date
F

Fabbe

Hi,

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

Is that possible?

Thanks.


Fabbe
 
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
 
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
 
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
 
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)
 
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
 
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
: >
: >
:
:
 
Back
Top