如果你只想得到最后的数字，我会使用Split而不是得到“第5个斜杠的索引”。
这将告诉你如何分割它。

Sub test()
  Dim s As String
  Dim v As Variant
  
  s = "https://www.walmart.com/cp/the-curl-shop/5859906"
  v = Split(s, "/")
  Debug.Print v(UBound(v))
End Sub

如果您想删除最后一个数字：

Debug.Print Replace(s, v(UBound(v)), "")

如果您正在尝试获取the-curl-shop，则：

Debug.Print v(4)

Sub test()
Dim sFindWhat As String
Dim sInputString As String
Dim targetNumber As String
Dim targetPosition As Long

sInputString = "https://www.walmart.com/cp/the-curl-shop/5859906"

'if you need just the final NUMBER use SPLIT
targetNumber = Split(sInputString, "/")(UBound(Split(sInputString, "/")))
Debug.Print targetNumber

'if you need the POSITION of last / use InStrRev
targetPosition = InStrRev(sInputString, "/")
Debug.Print targetPosition

End Sub

输出

Split function
InStrRev function

*已更新：看起来OP总是需要第五个斜杠，所以使用UDF来让它循环。你可以在宏甚至单元格上调用UDF：

Sub test()
Dim Test1 As String
Dim Test2 As String

Test1 = "https://www.walmart.com/cp/the-curl-shop/5859906"
Test2 = "https://www.walmart.com/browse/beauty/shampoo/1085666_3147628_5752434"

Debug.Print Test1, GET_FIFTH_SLASH(Test1)
Debug.Print Test2, GET_FIFTH_SLASH(Test2)

End Sub

Public Function GET_FIFTH_SLASH(ByVal vString As String) As Long
Dim i As Long
GET_FIFTH_SLASH = 0

'first loop gets position of first /
'second loop, starting at position of first /, will get position of second /
'and so on...
For i = 1 To 5 Step 1
    GET_FIFTH_SLASH = InStr(GET_FIFTH_SLASH + 1, vString, "/")
Next i

End Function

输出：

既然你已经在braXs answer的评论中描述了你实际想要的东西，也许这会达到你想要的效果：

Sub Test()
    Dim s As String
    Dim s2 As String

    s = "asdf/2345/asfd/asdf/todel/adsf/asfd/"
    s2 = "asdf/2345/asfd/asdf/12345/adsf/asfd/"

    Debug.Print Delete5thPartIfNotNumeric(s)
    Debug.Print Delete5thPartIfNotNumeric(s2)
End Sub

Function Delete5thPartIfNotNumeric(ByVal str As String)
    Dim parts As Variant

    Delete5thPartIfNotNumeric = str
    parts = Split(str, "/")
    If UBound(parts) < 4 Then Exit Function

    If Not IsNumeric(Replace(parts(4), " ", "")) Then
        parts(4) = ""
        Delete5thPartIfNotNumeric = Replace(Join(parts, "/"), "//", "/", , 1)
    End If
End Function

Option Explicit

Sub ModifyUrl()

    Dim url1 As String, url2 As String, regex, m, s As String
    url1 = "https://www.walmart.com/cp/abcdefh/5859906"
    
    ' capture between 4th and 5th /
    Const P = "(https://.*/.*/)(.*)/(.*)"
    
    With CreateObject("vbscript.regexp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = P ' pattern
        
        If .test(url1) Then
            Set m = .Execute(url1)
            ' between 4th and 5th /
            s = m(0).submatches(1)
        
            ' remove middle $2 if not numeric
            If Not IsNumeric(s) Then
                 url2 = .Replace(url1, "$1" & "$3")
            Else
                 url2 = url1
            End If
        End If
    End With
    MsgBox url1 & vbLf & url2, vbInformation, s

End Sub