+ Reply to Thread
Results 1 to 6 of 6

Removing returns

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    202

    Removing returns

    Hi All


    I use this code to search and replace a recurrent block of text in my documents :

    Please Login or Register  to view this content.

    It works well , but unfortunately it leaves some unevenly-spaced phrases with varying returns between them. Having found and removed the chunk of text in the search parameter , it's leaving a hole in the text.

    For example:

    Phrase 1

    Phrase 2



    Phrase 3


    Phrase 4

    Phrase 5


    There should be only one blank line between each phrase , but in some cases these are now more than this. Sometimes there are two returns , sometimes three. Can someone suggest some code , or an amendment to the code above , to remove the extra returns so that there is only one blank line between phrases?

    This would make the above example become even :

    Phrase 1

    Phrase 2

    Phrase 3

    Phrase 4

    Phrase 5



    Grateful for any advice.

    Last edited by CDandVinyl; 09-30-2020 at 03:19 PM.
    Using Excel , Word and Access 2003 - September 1st 2020

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,325

    Re: Removing returns

    You could try a find-and-replace for two paragraph returns with one paragraph return (Find ^p^p Replace with ^p) and run that after your code. Use the same code as you have above, but with these entries instead of the find/replace you have:
    Please Login or Register  to view this content.
    If you have instances of three or four paragraph returns, repeat the wdReplaceAll line a few times at the end, to reduce four to three to two to one.
    Please Login or Register  to view this content.
    Once you've got everything down to single paragraph returns, if you want to have a blank line between each (and therefore need two para returns), you'll need to run it again (once only) but replace ^p with ^p^p.

    Hope that makes sense!
    Regards,
    Aardigspook

    I've just moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    202

    Re: Removing returns

    Hi

    Many thanks for your help. I gave it all a go with some success , but the issue remains a little defiant. I'm wondering if the extra lines caused by my search and replace are paragraph returns after all.

    At any rate , I've attached a sample. Maybe this will help describe the issue more clearly.

    It's the three returns between item 3 and item 4 I'm trying to reduce to one. All of the rest of the page is as it should be.

    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,325

    Re: Removing returns

    You have some line breaks as well as paragraphs returns. You can see this if you turn on formatting marks. To do that, either press the 'show/hide' button on the Standard toolbar (normally just to the left of the % zoom box) or press Ctrl-Shift-8 (Ctrl-*).
    The symbol for that in Find-and-Replace is ^l instead of ^p (that's a lower-case l for look not a one or an upper-case I for If).
    So before doing the Find-Replace for ^p^p, first do one for Find ^l and Replace with ^p to convert all the line breaks to paragraph returns.

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    202

    Re: Removing returns

    Excellent - that's fixed it. Problem solved!



  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,325

    Re: Removing returns

    Glad I could help. Thanks for the rep and for marking the thread as Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Removing Duplicate Returns on Calculations (Not Duplicate Lines)
    By TeamOSupremeO in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-23-2018, 09:28 AM
  2. Removing carriage returns
    By RoystonDick in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 12-08-2015, 11:59 AM
  3. [SOLVED] Removing Chart Legend Entry when Series Returns "NA()"
    By James McEwan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-02-2015, 09:49 AM
  4. Replies: 0
    Last Post: 04-14-2014, 05:31 AM
  5. VBA Textbox - removing Carriage returns
    By madbloke in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-12-2013, 02:04 AM
  6. [SOLVED] Removing Carriage Returns in Excel 2000
    By Toby Stevenson in forum Excel General
    Replies: 6
    Last Post: 05-24-2011, 12:42 PM
  7. Removing Returns
    By yawnzzzz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2010, 05:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1