How to remove Alt+Enter character in a worksheet?

S

SteveW

Ok fair enough
More tricky, you'll have to have a macro for this

First what do you want to replace them with ? Space perhaps.
The Alt+Enter is only stored as a Newline by Excel

So something like this

Sub Remove_Newlines_From_Cell()
Dim myRange As Range
Set myRange = ActiveWindow.RangeSelection
'
For Each c In myRange.Cells
c.Value = Application.Substitute(c.Value, vbLf, " ")
Next
End Sub

Select a range, run the macro
You'll need to format each cell to have WrapText off
otherwise you may not see much change in the sheet

This could be acheived by adding
c.WrapText = False before the Next statement
Steve
 
B

Bernard Liengme

In the Find&Replace dialog for the Find What use ALT+0010 (you must use the
numeric keypad for this)
best wishes
 
D

Dave Peterson

Select your range to fix
Edit|Replace
what: ctrl-j (it may not look like anything in that box, but try it)
with: (leave blank -- or a space character??????)
replace all
 
S

SteveW

I'd forgotton you could put ctrl characters in Replace
and for those that wonder why Ctrl+J, not Ctrl+M (return)
is because Excel like most text handling software stores each line ending
in a Newline or Linefeed character
Ascii 10 (Ctrl J) or Alt+0010

Steve
 
G

Gord Dibben

Edit>Replace

What: Alt + 0010(on the numpad)

With: nothing or space.

You won't see anything in the dialog box but trust me..it works.

Replace all.


Gord Dibben MS Excel MVP
 
L

Lang Du

Thank you to all your answers.
I tried both VBA and Edit->Replace and they are worked for me. For
Edit->Replace, you can enter 0010 on the keyboard or numpad.
 
E

Eliot Leibowitz

Bernard, you just saved me HOURS of manual edits. I am actually doing the
reverse, replacing spaces with Alt-Enter. Alt+0010 did the trick. Thanks!
 
Top