Excel - Replace manual line breaks (Alt+Enter) with two spaces for selected cells

Asked By andreashermle on 21-Mar-10 07:04 AM
Dear Experts:

For selected cells in a column, I would like to replace any line
breaks (Alt + Enter) with two spaces using a macro solution. There are
cells that have multiple line breaks!

By the way: can this be achieved using the bulit-in search and
replace functionality as well?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas




joel replied to andreashermle on 21-Mar-10 07:26 AM
The return in the cell is code as an ascii character Linefeed which is
a 10. In the code below I set a variable to character 10.  You can also
use vblf.  You can use any string method with the linefeed like REPLACE
to remove the character or replace the character with other characters

LF = Chr(10)
or
LF = vbLF

with
Set c = .Columns("A").Find(what:=LF, _
LookIn:=xlValues, lookat:=xlPart)
if not c is nothing
FirstAddr = c.address
do
c = replace(c,LF, "  ")
set c = .Columns("A").findnext(after:=c)
while not c is nothing and c.address <> FirstAddr

end if
end with


The code below is complicated.  I just wanted to show a couple of
different methods.  I do not know how many cells you are trying to replce
the Linefeed with two spaces.  You can achieve the same thing as
follows

LF = vbLF

with sheets("sheet1")
columns("A").replace(LF,"  ")

end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=189290

http://www.thecodecage.com/forumz/chat.php
Chip Pearson replied to andreashermle on 21-Mar-10 08:10 AM
You can do it with code:

Sub AAA()
Dim R As Range
For Each R In Range("A1:A10")
If R.HasFormula = False Then
If R.HasArray = False Then
R.Value = Replace(R.Value, Chr(10), Space(2))
End If
End If
Next R
End Sub


Or you can do it manually. Select the cells to change, open the
Replace dialog (CTRL H), and with the cursor in the "Find What" box,
hold down the left ALT key and enter 0010 on the numeric keypad (to
the right of the main part of the keyboard, not the number keys above
the letters). You will not see anything in Find What text box, but the
character is there.  Then, in the Replace With text box, enter two
spaces. Click Replace All.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
Dave Peterson replied to andreashermle on 21-Mar-10 08:43 AM
Try recording a macro when:

You select the range
Edit|replace
Find what:     ctrl-j
replace with:  (spacebar)(spacebar)

Replace all

ctrl-j is the same as alt-0010 or alt-enter or =char(10).

It may not look like that inputbox on the replace dialog did not change, but try
it anyway.


--

Dave Peterson
andreashermle replied to Dave Peterson on 21-Mar-10 04:09 PM
ut try

Hi Dave,

thank you very much for your swift response. Your solution is the
first one I am trying out.
Great help. It works as desired.

Thank you very much for your professional help. Regards, Andreas
andreashermle replied to Chip Pearson on 21-Mar-10 04:13 PM
Hi Chip,

thank you very much for your great help. It works as desired.

I really do appreciate the time you experts take in answering these
questions.

Again, thank you very much. Regards, Andreas
andreashermle replied to joel on 21-Mar-10 04:17 PM
e
290

Hi Joel,

great help. Thank you very much for your professional advice. It works
as desired.

Regards, Andreas