Excel Function to extract a substring from a string ?

[Maverick27]

Can someone pls help me with a Excel Function (or VBA Code) to extract a substring from a string ?

I need the Text btwn the 1st ">" AND 2nd ">" from the RIGHT & output to another col. Example below.

String Example:
HEALTH & BEAUTY|HEALTH & BEAUTY > Health Care|HEALTH & BEAUTY > Mobility Devices > Wheel Chair

Substring Output:
Mobility Devices

Thanks.

 

[RoryA]

Here's a VBA method:

Function ExtractStringFromRight(ByVal sText As String, ByVal lNumber As Long, Optional sDelimiter As String = ">") As String
    Dim vParts
    vParts = Split(sText, sDelimiter)
    If UBound(vParts) >= lNumber Then
        lNumber = UBound(vParts) - lNumber + 1
        ExtractStringFromRight = Trim$(vParts(lNumber))
    End If
End Function

Then assuming your text is in A1, you'd enter:
=ExtractStringFromRight(A1,2)
in another cell.

 

[Maverick27]

Here's a VBA method:

Function ExtractStringFromRight(ByVal sText As String, ByVal lNumber As Long, Optional sDelimiter As String = ">") As String
    Dim vParts
    vParts = Split(sText, sDelimiter)
    If UBound(vParts) >= lNumber Then
        lNumber = UBound(vParts) - lNumber + 1
        ExtractStringFromRight = Trim$(vParts(lNumber))
    End If
End Function

Then assuming your text is in A1, you'd enter:
=ExtractStringFromRight(A1,2)
in another cell.

Thanks - it works.