Extract word that begins with the Nth specific character

[garryhe]

Hi Everyone!

I came across a problem that I need to extract all the text after the second last "/" in a link. For example:
"http://www.groupon.be/deals/antwerpen/hotel-kenzi-farah/30596211"
I need "hotel-kenzi-farah/30596211".

The total number of "/" is not constant, but what is certain is that the information after the second last "/" is all I need.

Really appreciate any help.

 

[mikerickson]

Try
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1)),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1),LEN(A1)))

 

[bhos123]

=mid(a1;find("@*";substitute(a1;"/";"@*";len(a1)-len(substitute(a1;"/";""))-1);1)+1;len(a1))

 

[Aladin Akyurek]

Shorter...

=REPLACE(A2,1,FIND("",SUBSTITUTE(A2,"/","",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1)),"")

 

[Aladin Akyurek]

Shorter...

=REPLACE(A2,1,FIND("",SUBSTITUTE(A2,"/","",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1)),"")

Using the backslash symbol causes the above line: backslash between quotes becomes a blank! Likely a board software issue.

=REPLACE(A2,1,FIND("\",SUBSTITUTE(A2,"/","\",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1)),"")

The backslash issue does not occur in between code tags...

 

[mohan.r1980]

Hello garryhe,

paste the below function in Module1

Function ExtractText(ByVal s As Range) As String

Dim iFindSlash As Integer, i As Integer, p As Integer, m As Integer
Dim iPos() As Integer

' extract count of Slash
For p = 1 To Len(s)
    If Mid(s, p, 1) = "/" Then
        m = m + 1
        iFindSlash = iFindSlash + 1
      ReDim Preserve iPos(m)
            iPos(m) = p
    End If
Next p


For i = LBound(iPos) + 1 To UBound(iPos)
        
  If i = UBound(iPos) - 1 Then
       ExtractText = """" & Mid(s, iPos(i) + 1, 100)
       Exit Function
  End If
Next i


End Function

now try in excel
i.e.

=ExtractText(A1)

 

[garryhe]

Thank you guys all. Many brilliant ideas!! Learnt a lot.
Thx again.