How to find multiple same char in a string in Excel?

[xhm]

Hello:

I am trying to locate the last subdirectory name from a full path. For
example, I have a full path like:

"c:\temp\temp1\temp2"

I would like to find the temp2 which is the last subdirectory.

I am thinking to use find() to find the last "\", and then use left(),
right() or mid() to obtain it. The problem is, when I tried to use
find() function, it only returns the first position of the "\" in the
full path string. How can I get the last "\" position in the string?

p.s., I had searched the Internet, but all the examples I found were
just had one occurance of the char to be found, while in my case, I
have multiple same chars ("\") in the string.

Thanks for any help in advance.

 

[Ron Rosenfeld]

Hello:

I am trying to locate the last subdirectory name from a full path. For
example, I have a full path like:

"c:\temp\temp1\temp2"

I would like to find the temp2 which is the last subdirectory.

I am thinking to use find() to find the last "\", and then use left(),
right() or mid() to obtain it. The problem is, when I tried to use
find() function, it only returns the first position of the "\" in the
full path string. How can I get the last "\" position in the string?

p.s., I had searched the Internet, but all the examples I found were
just had one occurance of the char to be found, while in my case, I
have multiple same chars ("\") in the string.

Thanks for any help in advance.

Try:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

which will return that last "\" separated substring from your string.
--ron

 

[Paul]

Take a look at INSTRREV

It'll find the last occurence
Repeat the task to find the second last and then extract the text from the
two points
Something like this :

cText = "c:\First Folder\Second Folder\File.xls"
nLast = InStrRev(cText, "\")
n2Last = InStrRev(cText, "\", nLast - 1)

cFolder = Mid(cText, n2Last + 1, nLast - n2Last - 1)

 

[Ryan H]

This will work for you. Hope this helps! If so, let me know, click "YES"
below.

Sub GetFileName()

Dim myFullPath As String
Dim myDirs As Variant
Dim myFileName As String

myFullPath = "C:\temp\temp1\temp2"

myDirs = Split(myFullPath, "\")

myFileName = myDirs(UBound(myDirs))

End Sub