Replace manual line breaks (Alt+Enter) with two spaces for selectedcells

[andreashermle]

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

 

[Chip Pearson]

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]

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 didn't change, but try
it anyway.

 

[andreashermle]

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 didn't change, but try
it anyway.

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]

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, LLCwww.cpearson.com







- Show quoted text -

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]

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 don't 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

Hi Joel,

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

Regards, Andreas