[VBA] Extract string after last "\" in string.

[RockandGrohl]

Hi guys,

I have a folder path and I'm just trying to extract the string after the last "\" in the string. I remember this needs something like Split & InStrRev but I can't quite remember how to do it as it's been a couple years since playing with VBA.

When I do something like

WeekData = InStrRev(MyFolder, "\")

it gives me the position of the last occurrence of the slash, in this instance it's position 106.

Thanks

 

[Rick Rothstein]

One way...

Arr = Split(MyFolder, "\")
WeekData = Arr(Ubound(Arr))

 

[Rick Rothstein]

If you wanted to do it using InStrRev...

WeekData = Mid(MyFolder, InStrRev(MyFolder, "\") + 1)

 

[RockandGrohl]

One way...

Arr = Split(MyFolder, "\")
WeekData = Arr(Ubound(Arr))

Unfortunately this comes up with ""

Here's my example string, just so we can work off the same page.

"C:\Users\Me\MyCompany\Reporting - Documents\Supplier Unbilled\2023-06\28-06-2023\"

What I'd like to be able to do is take the first two characters of the string using Left,2

So if the string was "15-04-2023 Report.csv" after the last slash, I just want the "15"

Ah ****, no I see the problem now... Ok all I need is that "28" lol.


Sorry, when you have a long string it doesn't show it all in the preview.. That stopped me seeing the last slash. I only printed it so I could grab it to show you.

 

[RockandGrohl]

Unfortunately this comes up with ""

Here's my example string, just so we can work off the same page.

"C:\Users\Me\MyCompany\Reporting - Documents\Supplier Unbilled\2023-06\28-06-2023\"

What I'd like to be able to do is take the first two characters of the string using Left,2

So if the string was "15-04-2023 Report.csv" after the last slash, I just want the "15"

Ah ****, no I see the problem now... Ok all I need is that "28" lol.


Sorry, when you have a long string it doesn't show it all in the preview.. That stopped me seeing the last slash. I only printed it so I could grab it to show you.

All sorted

Arr = Split(MyFolder, "\")
WeekData = WorksheetFunction.RoundUp(Left(Arr(UBound(Arr) - 1), 2) / 7, 0)

I'm taking the report date and figuring out which week in the month it belongs to

Thanks!