+ Reply to Thread
Results 1 to 11 of 11

Carriage returns (Enter) in a range

Hybrid View

mainemojo Carriage returns (Enter) in a... 02-07-2018, 10:01 AM
kev_ Re: Carriage returns (Enter)... 02-07-2018, 10:10 AM
mainemojo Re: Carriage returns (Enter)... 02-07-2018, 10:14 AM
kev_ Re: Carriage returns (Enter)... 02-07-2018, 10:32 AM
daboho Re: Carriage returns (Enter)... 02-07-2018, 11:21 AM
mainemojo Re: Carriage returns (Enter)... 02-07-2018, 12:28 PM
kev_ Re: Carriage returns (Enter)... 02-07-2018, 01:34 PM
mainemojo Re: Carriage returns (Enter)... 02-07-2018, 02:25 PM
mainemojo Re: Carriage returns (Enter)... 02-07-2018, 02:29 PM
kev_ Re: Carriage returns (Enter)... 02-07-2018, 02:30 PM
mainemojo Re: Carriage returns (Enter)... 02-07-2018, 02:35 PM
  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:

    My first line
    is continued
    on three lines.
    
    The weather
    is stormy outside.
    Should look like
    My first line is continued on three lines.
    
    The weather is stormy outside.
    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
    Forum Expert
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,513

    Re: Carriage returns (Enter) in a range

    Sub test()
    Dim y(), rg As Range, x, a, s, rw&, ii As Long, i As Long
        Set rg = Range("A1", Cells(Rows.Count, 1).End(3))
        x = rg.Value
        ReDim y(1 To rg.Rows.Count, 1 To 1)
        For ii = 1 To rg.Rows.Count
            rw = rw + 1
            a = Split(x(ii, 1), Chr(10))
                     For i = 0 To UBound(a, 1)
                           s = IIf(s = "", a(i), s & " " & a(i))
                     Next i
           y(rw, 1) = s: s = ""
        Next ii
     rg.Resize(UBound(y, 1), 1).Value = y
    End Sub
    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:

    "The Cisco Smart Install (SMI) feature is enabled on the remote host.
    If the SMI Client role enabled, it is subject to protocol misuse,
    which could allow an unauthenticated, remote attacker to:
    
      - Substitute the switch's startup-config file with a file that the
        attacker prepared and force a reload of the switch after a defined
        time interval.
    
      - Load the attacker-supplied IOS image onto the switch.
    
      - Execute high-privilege configuration mode CLI commands on the
        switch, including do-exec CLI commands.
    
      - Copy arbitrary files from the switch to the attacker-controlled
        TFTP server."
    Which would be better worded as:

    "The Cisco Smart Install (SMI) feature is enabled on the remote host. If the SMI Client role enabled, it is subject to protocol misuse, which could allow an unauthenticated, remote attacker to:
    
      - Substitute the switch's startup-config file with a file that the attacker prepared and force a reload of the switch after a defined time interval.
      - Load the attacker-supplied IOS image onto the switch.
      - Execute high-privilege configuration mode CLI commands on the switch, including do-exec CLI commands.
      - Copy arbitrary files from the switch to the attacker-controlled TFTP server."
    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:

    This test gives some information about the remote HTTP protocol - the
    version used, whether HTTP Keep-Alive and HTTP pipelining are enabled,
    etc... 
    
    This test is informational only and does not denote any security
    problem.
    Which should look like:
    This test gives some information about the remote HTTP protocol - the version used, whether HTTP Keep-Alive and HTTP pipelining are enabled, etc... 
    
    This test is informational only and does not denote any securityproblem.
    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

    Sub Test()
        With ActiveCell
            .Offset(, 1).Value = CleanTrim(.Value)
        End With
    End Sub
    Function CleanTrim(ByVal s As String) As String
        Dim X As Long, CodesToClean As Variant
        s = Replace(s, Chr(13), Chr(10))
        s = Replace(s, Chr(160), Chr(32))
        s = Replace(s, Chr(10) & Chr(10), "|||")
        s = Replace(s, Chr(10), Chr(32))
        s = Replace(s, "|||", Chr(10))
        CleanTrim = WorksheetFunction.Trim(s)
    End Function
    (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
    =cleantrim(A1)
    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:

    Sub Test()
        With ActiveCell
            ' .Offset(, 1).Value = CleanTrim(.Value)
            .Value = CleanTrim(.Value)
        End With
    End Sub
    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:

    Sub Test()
        Dim cell As Range
        For Each cell In Selection
            .Value = CleanTrim(.Value)
        Next cell
    End Sub
    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:

    Sub Test()
        Dim cell As Range
        For Each cell In Selection
            cell.Value = CleanTrim(cell.Value)
        Next cell
    End Sub

  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

    Sub Test()
        For Each cel In Selection
            With cel
                .Value = CleanTrim(.Value)
            End With
        Next cel
    End Sub

  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:

    Sub Test()
        Dim cell As Range
        For Each cell In Selection
            cell.Value = CleanTrim(cell.Value)
        Next cell
    End Sub

+ 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. 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