删除

Sid*_*out 7

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.

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

如果您仍然需要 VBA，请尝试使用此代码一次性更改整个列。此处给出了有关此方法的说明。

出于演示的目的，我假设数据是在列A的Sheet1。根据情况更改。

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

在行动：

我只将工作表的名称和范围更改为 L 和 L2:L。– 乌尔奇奥拉·希弗 17 分钟前

bra*_*raX 5

有不同的方法可以做到这一点，但这里是一种：

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

这是另一种方法：

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

---

用法：

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

或者：

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

docs.microsoft./en-us/office/vba/language/reference/user-interface-help/split-function

docs.microsoft./en-us/office/vba/language/reference/user-interface-help/mid-function