CODEWITHSUNDEEP
excelvba
Zanam
Zanam

Reputation: 4807

VBA Replace Only leading and trailing line breaks

I have a text as follows stored in a variable myStringVariable:

"


    About this item 
    This fits your . 
    Make sure this fits by entering your model number. 

"

Sorry I had to use "" above to display the leading and lagging line breaks I have in my cells which are generated though VBA macro.

How do I get rid of leading and lagging line breaks to get the following:

    About this item 
    This fits your . 
    Make sure this fits by entering your model number.

I am not sure how to approach this.

Edit:

Replace(myStringVariable, vbNewLine, "")

will replace all line breaks. So, I need a Trim function kind of solution I think.

Upvotes: 1

Views: 1510

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60364

Here is a trimCHAR function that works similarly to Excel's TRIM function, except you can specify the character to be TRIM'd.

It will remove all leading and trailing char, as well as any doubled char within the string (leaving a single char within the string, as TRIM does with spaces)

Function trimCHAR(ByVal S As String, char As String)
'similar to worksheet TRIM function except can specify character(s) to TRIM
  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 at end of string
        .Pattern = char & "*([^" & char & "]" & char & ")*"
        S = .Replace(S, "$1") 'Remove extra chars at start of string or within
End With
trimCHAR = S

End Function

Original

enter image description here

Usage on worksheet: =trimCHAR(cell_ref,CHAR(10))

in a macro, you might use =trimChar(myStringVariable, vblf)

Result

enter image description here

Upvotes: 3

Tragamor
Tragamor

Reputation: 3634

The following function may be of interest:

Function CleanTrim(ByVal Text As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
    ' https://excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092
    ' Code modified from that by Rick Rothstein
    Dim i As Long, CodesToClean As Variant
    CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                         21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
    If ConvertNonBreakingSpace Then Text = Replace(Text, Chr(160), " ")
    For i = LBound(CodesToClean) To UBound(CodesToClean)
        If InStr(Text, Chr(CodesToClean(i))) Then Text = Replace(Text, Chr(CodesToClean(i)), "")
    Next i
    CleanTrim = Application.WorksheetFunction.Trim(Text)
End Function

Upvotes: 1

FunThomas
FunThomas

Reputation: 29546

The following function will replace all leading and trailing newline and carriage returns. If you want, add more characters (eg space and Tabs) to the check.

Function TrimNewline(s As String) As String
     
    Dim i As Long, firstchar As Long, lastChar As Long
    For i = 1 To Len(s)
        Dim c As String
        c = Mid(s, i, 1)
        If c <> vbCr And c <> vbLf Then
            If firstchar = 0 Then
                firstchar = i
            Else
                lastChar = i
            End If
        End If
    Next i
    TrimNewline = Mid(s, firstchar, lastChar - firstchar + 1)

End Function

A test:

Sub test()
    Dim s As String
    s = vbCrLf & vbLf & vbCr & "(AAAAA" & vbCrLf & "BBB)" & vbCrLf & vbCrLf & vbCrLf
    Debug.Print "<" & TrimNewline(s) & ">"
End Sub

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96771

Give this a try:

Sub GiveMeABreak()
    Dim c As String, cell As Range, s As String
    
    c = Chr(10)
    For Each cell In Selection
        s = cell.Value
        If Left(s, 1) = c Then
            s = Mid(s, 2)
        End If
        If Right(s, 1) = c Then
            s = Left(s, Len(s) - 1)
        End If
        cell.Value = s
    Next cell
    
End Sub

NOTE:

Only the leading and trailing characters are tested. If there are multiple leading linebreaks, only the first will be removed.

EDIT#1:

This version of the code will remove all leading and trailing linebreaks:

Sub GiveMeABreak2()
    Dim c As String, cell As Range, s As String
    
    c = Chr(10)
    For Each cell In Selection
        s = cell.Value
        While Left(s, 1) = c
            s = Mid(s, 2)
        Wend
        While Right(s, 1) = c
            s = Left(s, Len(s) - 1)
        Wend
        cell.Value = s
    Next cell
    
End Sub

Upvotes: 1

Related Questions