Remove || (pipes) at the end of text inside a column

Question

I have this problem I can't seem to fix...

In column L I have certain roles, these roles are divided by || (pipes).

The problem: Some people deliver these roles they want to use like this:

Testing||Admin||Moderator||

But this doesn't work for the script we use to import these roles, what I would like to see is that whenever || (pipes) are used and after the pipes are used if there isn't any text following it up it should delete the pipes at the end.

What I tried is the find and replace option, but this also removes the pipes in between the text.

Hope someone can help me!

Problem:

Testing||Admin||Moderator||

Solution:

Testing||Admin||Moderator

Answer 1

A simple formula can solve your requirements

=IF(RIGHT(TRIM(A1),2)="||",LEFT(TRIM(A1),LEN(TRIM(A1))-2),A1)

The above formula is based on the below logic.

1. Check if the right 2 characters are ||
2. If "Yes", then take the left characters (LEN - 2)
3. If "No", then return the string as it is.

If you still want VBA then try this code which will make the change in the entire column in one go. Explanation about this method is given HERE.

For demonstration purpose, I am assuming that the data is in column A of Sheet1. Change as applicable.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lrow As Long
    Dim rng As Range
    Dim sAddr As String
    
    Set ws = Sheet1
    
    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Set rng = .Range("A1:A" & lrow)
        sAddr = rng.Address
        
        rng = Evaluate("index(IF(RIGHT(TRIM(" & sAddr & _
                              "),2)=""||"",LEFT(TRIM(" & sAddr & _
                              "),LEN(TRIM(" & sAddr & _
                              "))-2)," & sAddr & _
                              "),)")
    End With
End Sub

In Action:

I only changed the name of the worksheet and the range to L and L2:L. – Ulquiorra Schiffer 17 mins ago

Answer 2

There are different ways of doing this, but here is one:

Function FixPipes(val As String) As String
    Dim v As Variant
    
    v = Split(val, "||")
    If Len(v(UBound(v))) > 0 Then
      FixPipes = val
    Else
      FixPipes = Mid$(val, 1, Len(val) - 2)
    End If
End Function

Here's another way to do it:

Function FixPipes(val As String) As String
    If Mid$(val, Len(val) - 1, 2) <> "||" Then
      FixPipes = val
    Else
      FixPipes = Mid$(val, 1, Len(val) - 2)
    End If
End Function

---

Usage:

Sub test()
    Debug.Print FixPipes("Testing||Admin||Moderator||")
End Sub

Or:

Sub LoopIt()
    ' remove this line after verifying the sheet name
    MsgBox ActiveSheet.Name

    Dim lIndex As Long
    Dim lastRow As Long
    lastRow = Range("L" & Rows.Count).End(xlUp).Row
    
    For lIndex = 1 To lastRow
      Range("L" & lIndex) = FixPipes(Range("L" & lIndex))
    Next
End Sub

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-function