Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

What have you tried ?

F2, and backspace of Delete work fine for me

Steve


On Thu, 25 Jan 2007 15:56:01 -0000, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

but if your worksheet has thousands that characters.


"SteveW" wrote:

What have you tried ?

F2, and backspace of Delete work fine for me

Steve


On Thu, 25 Jan 2007 15:56:01 -0000, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

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


On Thu, 25 Jan 2007 16:32:01 -0000, Lang Du wrote:

but if your worksheet has thousands that characters.


"SteveW" wrote:

What have you tried ?

F2, and backspace of Delete work fine for me

Steve


On Thu, 25 Jan 2007 15:56:01 -0000, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.




--
Steve (3)

In the Find&Replace dialog for the Find What use ALT+0010 (you must use the
numeric keypad for this)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Lang Du" wrote in message
...
but if your worksheet has thousands that characters.


"SteveW" wrote:

What have you tried ?

F2, and backspace of Delete work fine for me

Steve


On Thu, 25 Jan 2007 15:56:01 -0000, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

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

Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

--

Dave Peterson

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
On Thu, 25 Jan 2007 17:10:10 -0000, Dave Peterson
wrote:

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

Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

EditReplace

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


On Thu, 25 Jan 2007 07:56:01 -0800, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

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.

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!

"Bernard Liengme" wrote:

In the Find&Replace dialog for the Find What use ALT+0010 (you must use the
numeric keypad for this)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Lang Du" wrote in message
...
but if your worksheet has thousands that characters.


"SteveW" wrote:

What have you tried ?

F2, and backspace of Delete work fine for me

Steve


On Thu, 25 Jan 2007 15:56:01 -0000, Lang Du wrote:

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.