Trim filename from filepath using VBA with range in specific column

[isritter]

Greetings all, I'm trying to trim a filename from a path using VBA with the filepath data located in column H. I've found and manipulated some code but cannot get this puppy to fire properly. What am I doing incorrectly and if anyone has the time to explain, why is what I have not working?

Please Login or Register  to view this content.

[HaHoBe]

Hi, isritter,

for me on a PC the sign to use is \ and not /. Maybe try and use Application.PathSeparator instead of any of these hard-coded signs. And you could use InStrRev to find the last occurrance from the right.

Ciao,
Holger

[LJMetzger]

Hi isritter,

Try changing the delimiter to a backslash:

Please Login or Register  to view this content.

Also see the following routines which help me extract parts of files:

Please Login or Register  to view this content.

Lewis

[Leith Ross]

Hello isritter,

Is your target file path a URL or local file? The delimiter for a URL is "/" while local (US) is "\".

Can you post a sample file path?

[isritter]

Thank you all for the replies. Leith Ross, the file path uses "/" as a separator. It's a file, not a URL that I'm after. There is a header in H1 which is text so I'm unsure if that has anything to do with it. Any ideas?

[Leith Ross]

Hello isritter,

I would need to see an example of the 'header".

[isritter]

It might also be noteworthy to mention that I'm exporting data from another source that I'm cleaning up so the path identifies a username so it looks something like this...

Please Login or Register  to view this content.

I'll try and post a sample path when I get home but hopefully this sheds some light on what I'm trying to do.

The header, located in H1 just says something like 'Username'.

[isritter]

Here's the path that's listed...

Please Login or Register  to view this content.

The Header text, located in H1 is "Username". I'm trying to pull TomJones from the end of the path.

I've adjusted the delimiter to turn both ways but either way, all I get is "Delimiter Not Found" in the first five cells of Column H. Thoughts?

[Leith Ross]

Hellp isritter,

Try this method...

Please Login or Register  to view this content.

[isritter]

That didn't work either... is it possible that the path is somehow not reading as text?

[Leith Ross]

Hellp isritter,

Looking at the code you posted, you are passing the string by reference (the default). The call will fail if the variable you are trying to pass has not be declared. If this fails, you should your workbook for review.

Please Login or Register  to view this content.