How to remove last name from path in VBA

Question

I am new to VBA, I am working on the task below.

nPath = "Root\zTrash - No longer needed\NOC\NOC"

I want to remove \NOCfrom nPathto display only Root\zTrash - No longer needed.

I am using this code:

=(Left(nPath, InStrRev(nPath, "\") - 1))

But I only get NOC

Answer 1

How about this?

Left(nPath, InStr(nPath, "\NOC") - 1)

<h / "> You use InStrRevto find the last one \- this is not what you want. Use InStrc \NOCto find the first instance\NOC

Answer 2

You can do this in two steps:

Dim newString as String
newString = Left(nPath, InStrRev(nPath, "\") - 1)
newString = Left(newString , InStrRev(newString , "\") - 1)