Replace manual line breaks (Alt+Enter) with two spaces for selected cells
in [Programming]
|
Prev: Help with DDE - stock feed
Next: Replace manual line breaks (Alt+Enter) with two spaces for selectedcells
From: andreashermle on 21 Mar 2010 07:04 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
From: Dave Peterson on 21 Mar 2010 08:43 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
From: andreashermle on 21 Mar 2010 16:09 On Mar 21, 1:43 pm, Dave Peterson <peter...(a)verizonXSPAM.net> 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
From: andreashermle on 21 Mar 2010 16:13
On Mar 21, 1:10 pm, Chip Pearson <c...(a)cpearson.com> 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 > > > > <andreas.her...(a)gmx.de> 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 |