Extracting substring between two slashes in a file path

[JaceyK]

So we are wanting to grab data between two back slashes - specifically the last back slash and the next one before that. Ex: C:\data\folder\text\file\document_xlsx\ - we want to extract document_xlsx into another field. I know I can use Update query and instrrev function but I am just not getting what I need - guessing its more complex? Oh and it will vary in length, obviously. Can anyone help? Please?

[Welshgasman]

I would probably use the Split() function if not the InstrRev() starting at Len() -1

[Micron]

To get the last you can use -1 but to get the one before that, you will have to calculate the next position to start from. That might be InstrRev of the Len of your path minus the number that the first instrrev returns. A lot simpler if this "C:\data\folder\text\file" is constant.

[Gicu]

Something like this would work:

Expr1: Replace(Mid("C:\data\folder\text\file\document_xls x",InStrRev(Left("C:\data\folder\text\file\documen t_xlsx",Len("C:\data\folder\text\file\document_xls x")-1),"")),"","")

If you need to apply it to a field it looks a bit better:
Expr1: Replace(Mid([YourField],InStrRev(Left([YourField],Len([YourField])-1),"")),"","")

Cheers,

[moke123]

I use something like this

Function fGetElement(strIN As String, strSeparator As String, Optional intSegment As Integer = 0) As String
'intSegment is 0 based reading right to left

    Dim var As Variant, i As Integer
    
    If Right(Trim(strIN), 1) = strSeparator Then strIN = Left(strIN, Len(strIN) - 1)

    var = Split(strIN, strSeparator)

    i = UBound(var)

    fGetElement = var(i - intSegment)

End Function

?fGetElement("C:\data\folder\text\file\document_xlsx\","\")
document_xlsx

?fGetElement("C:\data\folder\text\file\document_xlsx\","\",1)
file

?fGetElement("C:\data\folder\text\file\document_xlsx\","\",2)
text

[CJ_London]

or in one line

?split("C:\data\folder\text\file\document_xlsx\","")(ubound(split("C:\data\folder\text\file\document_xlsx\",""))-1)
document_xlsx

[moke123]

Oops. My bad. I read the original post as they wanted the last element and the one before it.
My function is generic where you can have any character separating the values.