+ Reply to Thread
Results 1 to 11 of 11

Carriage returns (Enter) in a range

  1. #1
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Carriage returns (Enter) in a range

    I have cells within a worksheet that have text such as:

    Please Login or Register  to view this content.
    Should look like
    Please Login or Register  to view this content.
    What I think I need is VBA that...
    1. delete a char(10)
    2. replace char(10) followed by a space with only one space
    3. replace two consecutive char(10) with only one char(10)


    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Carriage returns (Enter) in a range

    Are those multiple sentences inside the same cell or 2 cells each with a sentence?
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Carriage returns (Enter) in a range

    Same cell. Sorry. Not all the cells are like that. Some don't have a chr(10) at all in them. Others have much more text and carriage returns. I would prefer to be able to select cells to run this on.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Carriage returns (Enter) in a range

    So would this get us there?
    - remove ALL line breaks
    - insert line break after every full stop (Period)

    Do you want an empty line between each sentence?

  5. #5
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,241

    Re: Carriage returns (Enter) in a range

    Please Login or Register  to view this content.
    Last edited by daboho; 02-07-2018 at 11:40 AM.

  6. #6
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Carriage returns (Enter) in a range

    Here is an better example of cell content:

    Please Login or Register  to view this content.
    Which would be better worded as:

    Please Login or Register  to view this content.
    So to your questions:
    - remove ALL line breaks - Not ALL. Perhaps it should be only if the break is preceded by anything other than period or semicolon, then delete the break.
    - insert line break after every full stop (Period) - This could be modified so if it is a period (or a colon or semicolon) followed by two consecutive breaks, then leave the period, but only remove one of the two breaks.
    Do you want an empty line between each sentence? - No, as shown by the example.

    As for my original items.
    1. delete a char(10) - same as your first item - I really shouldn't do this one.
    2. replace char(10) followed by a space with only one space - Think this would be better that if the sentence beings with " - " and ends with two chr(10), replace with only one chr(10).
    3. replace two consecutive char(10) with only one char(10) - this one should probably be skipped as it could create other issues.

    Besides the example above, another is:

    Please Login or Register  to view this content.
    Which should look like:
    Please Login or Register  to view this content.
    Maybe it would be easiest to just have it:
    • if one break, delete it
    • if two breaks, delete only one
    • if three breaks, delete only one
    Last edited by mainemojo; 02-07-2018 at 12:49 PM.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Carriage returns (Enter) in a range

    See how close this gets you
    Run from cell containing the text
    Amended text is dumped in adjacent cell to right
    Make sure receiving cell is a sensible width to prevent ridiculous row heights

    Please Login or Register  to view this content.
    (May need to insert an additional line break after a period and a colon etc)

    OR
    If you place above code in a general module, the function can be used in a normal Excel formula
    So with text in cell A1, and this formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kev_; 02-07-2018 at 02:26 PM.

  8. #8
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Carriage returns (Enter) in a range

    Perfect! Thank you! I just modified the sub to overwrite (now that it's been tested) to:

    Please Login or Register  to view this content.
    When I select multiple cells, if I run the code as is, it only runs on the first cell. How do I get it to loop through each one? If I modify to below, I get a compile error (invalid or unqualified reference) with the bold:

    Please Login or Register  to view this content.
    Last edited by mainemojo; 02-07-2018 at 02:27 PM.

  9. #9
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Carriage returns (Enter) in a range

    Figured it out! Modified it to below:

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Carriage returns (Enter) in a range

    Try this - overwrites selected cells

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    07-01-2009
    Location
    Maine, USA
    MS-Off Ver
    Excel 2016
    Posts
    161

    Re: Carriage returns (Enter) in a range

    Final code is:

    Please Login or Register  to view this content.

+ 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. Carriage Returns when concatenating
    By Wendy Collins in forum Excel General
    Replies: 2
    Last Post: 11-17-2011, 01:48 PM
  2. Carriage Returns
    By Highbury_White in forum Excel General
    Replies: 3
    Last Post: 02-24-2010, 10:09 AM
  3. How Do I Get Rid of Carriage Returns?
    By Ocean Zhang in forum Excel General
    Replies: 23
    Last Post: 07-02-2009, 10:49 AM
  4. Carriage returns in the formula bar?
    By rubeus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-06-2006, 02:00 PM
  5. [SOLVED] Delete Carriage Returns
    By Andre in forum Excel General
    Replies: 4
    Last Post: 12-01-2005, 07:35 PM
  6. Replies: 3
    Last Post: 11-17-2005, 03:10 PM
  7. VBA: Concatenate with carriage returns
    By Rob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2005, 09:05 AM
  8. Carriage returns
    By tracyt620 in forum Excel General
    Replies: 1
    Last Post: 07-07-2005, 09:52 AM

Tags for this Thread

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