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

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/sh...d.php?t=189290

http://www.thecodecage.com/forumz/chat.php

On Mar 21, 12:26*pm, joel wrote:
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/sh...d.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

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





On Sun, 21 Mar 2010 04:04:44 -0700 (PDT), andreashermle
wrote:

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

On Mar 21, 1:10*pm, Chip Pearson wrote:
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

On Sun, 21 Mar 2010 04:04:44 -0700 (PDT), andreashermle



wrote:
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- Hide quoted text -

- 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

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 wrote:

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

--

Dave Peterson

On Mar 21, 1:43*pm, Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

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