Getting Path Information From A Text String

[saltkev]

Good Morning

I hope someone can help. I have a file path as shown below. I need to remove the file name from the end leaving just the path as shown in the second line. The path and file names will change.


C:\Users\keames1\Desktop\2 - Current DD + N R9\WCT Station 130 DDR9.xlsm

C:\Users\keames1\Desktop\2 - Current DD + N R9\ <tbody> </tbody>

<tbody>
</tbody><colgroup><col></colgroup>

Many Thanks

<tbody>
</tbody><colgroup><col></colgroup>

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B
1	C:\Users\keames1\Desktop\2 - Current DD + N R9\WCT Station 130 DDR9.xlsm	C:\Users\keames1\Desktop\2 - Current DD + N R9\
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(A1,"",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,"",""))),255))&""

 

[pgc01]

Hi FormR, Why the &"" at the end?

 

[FormR]

Hi FormR, Why the &"" at the end?

Hi Pgc - there should be a backslash within the double quotes - the forum must have parsed it thinking it was HTML.

=TRIM(LEFT(SUBSTITUTE(A1,"\",REPT(" ",255),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),255))&"\"

Thanks for spotting.

EDIT: it got parsed in a few places - updates in red.

 

[pgc01]

You are right, I had not examined the formula just saw the &"" at the end.

But now I looked at the formula and you miss another \ in the Substitute. Must be the same problem as the other.

EDIT: I see that you already spotted it.

 

[saltkev]

Thanks For The Input Guys. However, I was looking for a VBA Solution, I already have a cell based solution.

Again Many Thanks

Kev

 

[FormR]

I was looking for a VBA Solution

Hi, that's always worth mentioning at the outset.

One option, for example:

Dim S As String
S = "C:\Users\keames1\Desktop\2 - Current DD + N R9\WCT Station 130 DDR9.xlsm"
MsgBox Left(S, InStrRev(S, "\"))

 

[saltkev]

Worked a treat thanks