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

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
0
andreashermle
3/21/2010 11:04:44 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1588 Views

Similar Articles

[PageSpeed] 49

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

0
joel
3/21/2010 11:26:57 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





On Sun, 21 Mar 2010 04:04:44 -0700 (PDT), andreashermle
<andreas.hermle@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
0
Chip
3/21/2010 12:10:35 PM
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
0
Dave
3/21/2010 12:43:32 PM
On Mar 21, 1:43=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Try recording a macro when:
>
> You select the range
> Edit|replace
> Find what: =A0 =A0 ctrl-j
> replace with: =A0(spacebar)(spacebar)
>
> Replace all
>
> ctrl-j is the same as alt-0010 or alt-enter or =3Dchar(10). =A0
>
> It may not look like that inputbox on the replace dialog didn't change, b=
ut 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!
>
> > =A0By 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
0
andreashermle
3/21/2010 8:09:56 PM
On Mar 21, 1:10=A0pm, Chip Pearson <c...@cpearson.com> wrote:
> You can do it with code:
>
> Sub AAA()
> Dim R As Range
> For Each R In Range("A1:A10")
> =A0 =A0 If R.HasFormula =3D False Then
> =A0 =A0 =A0 =A0 If R.HasArray =3D False Then
> =A0 =A0 =A0 =A0 =A0 =A0 R.Value =3D Replace(R.Value, Chr(10), Space(2))
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 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. =A0Then, in the Replace With text box, enter two
> spaces. Click Replace All.
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> =A0 =A0 =A0 =A0 Excel, 1998 - 2010
> Pearson Software Consulting, LLCwww.cpearson.com
>
> On Sun, 21 Mar 2010 04:04:44 -0700 (PDT), andreashermle
>
>
>
> <andreas.her...@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
0
andreashermle
3/21/2010 8:13:49 PM
On Mar 21, 12:26=A0pm, joel <joel.486...@thecodecage.com> 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. =A0You can also
> use vblf. =A0You can use any string method with the linefeed like REPLACE
> to remove the character or replace the character with other characters
>
> LF =3D Chr(10)
> or
> LF =3D vbLF
>
> with
> Set c =3D .Columns("A").Find(what:=3DLF, _
> LookIn:=3DxlValues, lookat:=3DxlPart)
> if not c is nothing
> FirstAddr =3D c.address
> do
> c =3D replace(c,LF, " =A0")
> set c =3D .Columns("A").findnext(after:=3Dc)
> while not c is nothing and c.address <> FirstAddr
>
> end if
> end with
>
> The code below is complicated. =A0I just wanted to show a couple of
> different methods. =A0I don't know how many cells you are trying to replc=
e
> the Linefeed with two spaces. =A0You can achieve the same thing as
> follows
>
> LF =3D vbLF
>
> with sheets("sheet1")
> .columns("A").replace(LF," =A0")
>
> end with
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/showthread.php?t=3D189=
290
>
> 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

0
andreashermle
3/21/2010 8:17:50 PM
Reply:

Similar Artilces:

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

Setting replies as a default other than to sender address, rather than manually?
Today is the last day of my contract, just out of curiousity, is there a way to change the "reply to" as a default? I'm going to try to remember at the end of the day when I send my last emails from a place of work to friends and family, to each time go in and change the "reply to" to my home email but I'd like to change it "permanently" at that time to make it one less thing to have to think about. Can this be done? [I'm not concerned with settings after that, as my account will be de-activated so no worries if I forget to change it back. <g&...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Links into blank cells
I have a formula which sums 2 or more cells in a separate worksheet. However if any of these cells are blank the formula returns a '#VALUE'. Bizarrely the same formula into a different worksheet does not cause this error message. Any idea's other than a long IFISERROR formula? The cells in the separate worksheets may contain a blank space, as if someone typed just the space bar and pressed return. That cell would look blank, visually, but Excel would evaluate it as a text entry and return the VALUE error because Excel doesn't perform math on a text string (without some ext...

Combining two similar worksheets and showing changes
I thought i posted this question yesterday, obviously i have no idea what im doing, because now i can't find it. But anyway, In excel I have two very similar worksheets that started out as the same worksheet. They were copied by someone and now two users have been making changes to them, and i guess its my job to put them back together, and show the changes that both of the users made. if i just copied one to the other i would have nearly five thousand rows of duplicate data. How can i do this? It seems like it would be so simple, I just don't work with excel that much. th...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Page Number Watermark in Page Break Preview #2
It's making me crazy to. Does anyone have an answer? Pamela What's the question? Gord Dibben Excel MVP On Wed, 18 Feb 2004 10:16:05 -0800, Pamela <anonymous@discussions.microsoft.com> wrote: >It's making me crazy to. Does anyone have an answer? I don't understand this question? >-----Original Message----- >It's making me crazy to. Does anyone have an answer? >. > ...

Formula change from cell reference to count
Formula works as is: If sheet exist matching the name in A45, then look at K39 on that sheet and return the value. I need to ammend to say: If sheet exist with matching the name in A45, then count the number of times the name is listed in Column N (or range n2:n500) on that sheet. =IF(ISERROR(CELL("address",INDIRECT("'" & A45& "'!k$39")))," ",INDIRECT ("'" &A45& "'!k$39")) First, I returned "" instead of " " (I don't like spaces!). =IF(ISERROR(CELL("address",INDI...

how do you "select locked cells" w/o "select unlocked cells"?
I am using Excel 2003. I am attempting to protect only certain cells within an Excel worksheet. I first click on FORMAT, CELLS, and "deselect" the "LOCKED" check box. I then highlight the cells I wish to protect, repeat that step, only this time make sure the LOCKED check box is checked. I then go to to TOOLS, PROTECTION, PROTECT SHEET, and attempt to select the option, and JUST the option for "Select locked cells", however, everytime I select this option, a checkmark appears next to "Select unlocked cells" as well. One seems to trigger the othe...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

I enter date 082305 xl changes it to 5/4/25
I set the date format for the cell as: 8/23/05. When I enter date 82305, I get 5/4/25. I've checked everything I can think of but I haven't a clue as to what is causing this. Thanks for all help, "Monalisa18" wrote: > I set the date format for the cell as: 8/23/05. When I enter date 82305, I > get 5/4/25. I've checked everything I can think of but I haven't a clue as to > what is causing this. Thanks for all help, Monalisa You will have to enter the slashes to get the correct date. Excel calculates date from the 1/1/1901 and this is given the refe...

Moving cell formula 03-17-10
I have a formula that calculates averages each week. I need a formula that automatically uses the cell below when new data is entered. Hi, Average is clever enough to ignore empty cells so why not =AVERAGE(A:A) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "iamjbunni" wrote: > I have a formula that calculates averages each week. I need a formula that > automatically uses the cell below when new data is entered. "cell belo...

breaking up a phone number
I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25877 View this thread: http://www.excelforum.com/showthread.php?threadid=392596 ...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

trendlines for two phases
Hello. I am working on a line chart that shows 12 weeks of data for homework completion. The X axis is weeks and the Y axis is rate of completion. Weeks 1-6 were baseline and data was collected for completion. Weeks 7-12 were experimental where an intervention was used to influence homework completion. I would like to be able to insert a trend line for weeks 1-6 and a separate trend line for weeks 7-12 on the same graph to compare student performance. Right now, when I right-click on a data point in my grapn to create a trendline, I get a trendline for all 12 weeks of data. Can a...

rows break across printed page
Is there a way to set a document so that when you print, the rows (and cells, of course) never break across pages? I'm not generally an Excel user, so the mysteries of the app befudle me. Thanks very much in advance. While rows can be set to break across pages (or not) in Word, this is not possible in Excel--a single row will not break. Thus there is no need to make any document setting. If you want to keep several rows together on a page, go to the View menu and select Page Break Preview. There, you can change the break points for rows, being careful of bottom page margins. "...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

OL 2003 command line
Is it just something wrong with my installation, or is the /recycle command line switch broken in OL 2003? It used to be (with OL XP) that /profile profilename /recycle would load OL with profile name "recycling" (using) the open instance of outlook, essentially replacing the profilename in use. Now, it seems that it's being ignored, and using the above command line simply restores the already open instance of Outlook, leaving its currently loaded profile as-is. Help suggests that /recycle is still supported. ...

Inserting Pagebreaks every 40 lines...
Hello all, I'm looking to find a way to simply insert pagebreaks every 40th row. Is there a way to do this programatically? Any assistance would be much appreciated. Thanks. Hi Trevor: Not elegant but it works: Sub Macro1() ' Macro recorded 19/01/2004 by Bernard Liengme Range("A41").Select For j = 1 To 100 ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell ActiveCell.Offset(rowOffset:=40, columnOffset:=0).Activate Next j End Sub You really need to locate where the last used row is, bit this will get you going. -- Bernard Lieng...

XL XP dissapearing (hidden) numeric values, cell width
This is a newbie question, but... I was just upgraded to Windows and Office XP, and am working on a workbook to help someone out at work. I have several very narrow columns (for formatting reasons that affect other rows) My target cell is right justified, so that anything that shows will overlap to the left (right side of cell is the border of print area) When I put text in the cell, it overlaps to the left as expected. With numbers, the numbers don't overlap the cell border, and are therefore not visible (similar to when you have a number too large for a regular-sized column, it shows...

I am having problems with line spacing in Publisher.
I am having a problem with Publisher 2003 and the spacing of the last lines of paragraphs. I am using a catalogue template to produce a "facebook" of people and information about them. At every last paragraph line, there is about 1/3 to 1/2 of an extra line's worth of space. The rest of the paragraph is fine; only the last line does this. I have tried, in the "Style and Formatting" box, checking the "Widow and Orphan Control" item, as well as the "Keep lines together" item. It doesn't seem to work. The style that I am using is "Accent ...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Bottom line of page border does not print
The page border prints only the top and side lines. Error message says it is outside of the printable page. How do I get the whole border to print? Ed wrote: > The page border prints only the top and side lines. Error message > says it is outside of the printable page. How do I get the whole > border to print? See http://www.word.mvps.org/FAQs/Formatting/BottomsDontPrint.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. ...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...