How can I remove line breaks from a cell but keep the address format in the same cell VBA

The cell I'm trying to remove line breaks from is this:

(Blank line break)
Test Company
1640 Test Avenue,
New York, NY 10035
(000)123-456    
(Blank line break)

I want to remove the two rows that have line breaks. Ive tried to use find and replace to replace the line breaks with alt code 0010 but it creates a long string.

I want to end up with this:

Test Company
1640 Test Avenue,
New York, NY 10035
(000)123-456  

Any help would be appreciated.

2 answers

  • answered 2018-10-11 19:36 Tim Williams

    Something like this

    Dim v
    v = Trim(cell.Value)
    if left(v,1)=vblf then v = right(v, len(v)-1)  'remove leading break
    if right(v,1)=vblf then v = left(v, len(v)-1)  'remove trailingbreak
    v = replace(v, vbLf & vbLf, vbLf)              'remove blank lines
    cell.value=v
    

  • answered 2018-10-12 11:03 Ron Rosenfeld

    Here is a UDF that works similar to the worksheet TRIM function, but allows you to specify the character to trim:

    Option Explicit
    Function trimCHAR(ByVal S As String, char As String)
      Dim RE As Object
      Dim I As Long
    
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .Global = True
        .MultiLine = True
    
    'need to do separately, otherwise multiple chars within will
    'be removed
            .Pattern = char & "$"
            S = .Replace(S, "") 'Remove extra chars within or at end of string
            .Pattern = "^" & char
            S = .Replace(S, "") 'Remove extra chars at start of string
    End With
    trimCHAR = S
    End Function
    

    In your situation, you could use it like:

    =trimCHAR(cell_ref,CHAR(10))
    

    OR you could use it as part of a macro that iterates through all the cells to be processed.

    The idea for this came from Power Query's Text.Trim function which only works on the beginning and end of the string, but allows any character string to be specified; along with an adaptation of that by Ken Puls, which adds the worksheet function TRIM feature of removing sequential "chars" within the string with only a single one.