Help parsing a string

[KGee]

I have a folder path saved as a variable and want to keep the part after the \ which follows folder 2. Is there a way to count the number of "\'s" in the string and give me everything that follows after the sixth occurrence of the slash? I want to repeat this code in different areas and the folder names vary as well as the length so I can't specify a starting character position.

Original string:

\\server\share\folder1\folder2\folder3\folder4\folder5\

Desired output:

folder3\folder4\folder5\

 

[Andrew Poulsom]

Try eg:

=MID(A1,FIND("|",SUBSTITUTE(A1,"\","|",6))+1,255)

 

[Haseeb Avarakkan]

Try,

=REPLACE(A1,1,FIND("^",SUBSTITUTE(A1,"\","^",6)),"")

 

[Misca]

In VBA you can also use a loop to get the desired string:

For i = 1 To 6
    MyString = Mid(MyString, InStr(1, MyString, "\") + 1, Len(MyString))
Next i

 

[KGee]

In VBA you can also use a loop to get the desired string:

For i = 1 To 6
    MyString = Mid(MyString, InStr(1, MyString, "\") + 1, Len(MyString))
Next i

I was just going to ask if there was a VBA equivalent for substitute as I need to do this in VBA. Both of the formula options posted also work.

Thanks!

 

[Andrew Poulsom]

You can use Substitute in VBA if you qualify it with the WorksheetFunction object.