Function prova(a As Range)
Dim i As Integer
For i = Len(a.Value) To 1 Step -1
    If Mid(a.Value, i, 1) = "|" Then
        prova = Left(a.Value, i - 1)
        Exit For
    End If
Next i
End Function

Public Function CancLastPipe(ByVal sString As String) As String
  'by scossa
  'w w w .excelvba.it/Forum/thread.php?f=1&t=12076
    
  Dim re As RegExp  'richiede il riferimento a Microsft VBscript Regular Exspression 5.5
                    'altrimenti
                    'Dim re as Object
                    
  Dim reMatch As MatchCollection  'richiede il riferimento a Microsft VBscript Regular Exspression 5.5
                                  'altrimenti
                                  'Dim reMatch as Object
  
  Dim sMatched As String
  Dim sPatt As String
  
  On Error GoTo Sost_Error_
  
  Set re = New RegExp             'richiede il riferimento a Microsft VBscript Regular Exspression 5.5
                                  'altrimenti
                                  'set re = CreateObject("vbscript.regexp")
  
  With re
    .Global = True
    sPatt = "s*|(s*w+s*)*$"
    .IgnoreCase = True
    .Pattern = sPatt
    If .Test(sString) Then
      Set reMatch = .Execute(sString)
      sMatched = reMatch.Item(0)
      CancLastPipe = .Replace(sString, "")
    Else
      CancLastPipe = sString
    End If
  End With
  On Error GoTo 0

Sost_Error_:

  Set re = Nothing
  Set reMatch = Nothing

End Function

Option Explicit

Sub test()
Dim re As Object, v As Variant, arr As Variant

    Set re = CreateObject("VbScript.regexp")
    re.Global = True
    re.Pattern = "|(?!.*|)"
    
    arr = Array("Hello", "Hello | world ", "Hello| world |", "Hello | world | hello")
    For Each v In arr
        re_execute re, (v)
    Next
    
End Sub

Private Sub re_execute(re As Object, s As String)
Dim ma As Object
    For Each ma In re.Execute(s)
        Debug.Print s, "in pos. "; ma.firstindex, "--> "; Left(s, ma.firstindex - 1)
    Next
    
End Sub