Hard return

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ttribble - 19 Jun 2007 01:31 GMT

Does any one out there know how to remove a lot of hard returns from multiple
cells

Reply to this Message

Gord Dibben - 19 Jun 2007 01:50 GMT

Usually Edit>Replace works on the selection if the hard return is generate by
Alt + Enter in a cell.

What: CTRL + j

With: nothing

Replace all.

If generated by CHAR(10) in a formula, copy>paste special>values first.

Gord Dibben MS Excel MVP

>Does any one out there know how to remove a lot of hard returns from multiple
>cells

Reply to this Message

Dave Peterson - 19 Jun 2007 01:57 GMT

Or replace it with a space character?

(just in case <vbg>)

> Usually Edit>Replace works on the selection if the hard return is generate by
> Alt + Enter in a cell.
[quoted text clipped - 11 lines]
> >Does any one out there know how to remove a lot of hard returns from multiple
> >cells

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 19 Jun 2007 04:33 GMT

I just never remember to add that piece of info<g>

Thanks, Gord

>Or replace it with a space character?
>
[quoted text clipped - 15 lines]
>> >Does any one out there know how to remove a lot of hard returns from multiple
>> >cells

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Jun 2007 01:57 GMT

> Does any one out there know how to remove a lot of
> hard returns from multiple cells

You could run a macro similar to this (change the indicated range as
needed)....

Dim C As Range
For Each C In Range("b1:f3")
C.Value = Replace(C.Value, vbLf, " ")
Next

and then save the worksheet afterwards. The above macro removes each Line
Feed (what you are calling a "hard return") and replaces them with a single
blank space.

Rick

Reply to this Message