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)
LF = vbLF

Set c = .Columns("A").Find(what:=LF, _
LookIn:=xlValues, lookat:=xlPart)
if not c is nothing
FirstAddr = c.address
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

LF = vbLF

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

end with

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

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.

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

You select the range
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

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

Hi Joel,

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

Regards, Andreas