VBA function to read a Cell value from Right to left until the first '\' is found

[NessPJ]

Hello all,

Is there some way i could create a function to read a cell value from right to left until the first '\' is found.

So for example if i have a cell value that states: G:\application\log\log220620.txt
the function will only return "log220620.txt" ?

 

[RoryA]

InStrRev will find the position of a character searching from right to left, though the number returned is the position from left to right, so you can then use that with Mid$ to get the last part of the text - basically Mid$(text, instrrev(text, "\")+1). You could also use Split to convert the text to an array, and then just return the last item:

Dim parts
parts = Split(text, "\")
msgbox parts(ubound(parts))

 

[NessPJ]

InStrRev will find the position of a character searching from right to left, though the number returned is the position from left to right, so you can then use that with Mid$ to get the last part of the text - basically Mid$(text, instrrev(text, "\")+1). You could also use Split to convert the text to an array, and then just return the last

Thanks! I did not know the Instrrev and MID function could work together this easily. This works fine!

Not to be a nag, but is it simple to also create a function that would return everything before the last '\' as well ?

 

[RoryA]

Yes, Just use Left$ instead of Mid$ and subtract 1 from the result of InstrRev instead of adding 1.