Extract file name

[Steven]

What function would extact the name: 84DTCC 2008_05
out of this.
G:\CorporateAcct\SMR\Special\Support\84DTCC 2008_05.pdf

Note that the directory and subdirectory(ies) will not be constant (ie.
some may have more or less "\" )

Thank you,

Steven

 

[Ron Rosenfeld]

What function would extact the name: 84DTCC 2008_05
out of this.
G:\CorporateAcct\SMR\Special\Support\84DTCC 2008_05.pdf

Note that the directory and subdirectory(ies) will not be constant (ie.
some may have more or less "\" )

Thank you,

Steven

It appears that the name will occur after the last "\".

So:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

--ron

 

[Rick Rothstein \(MVP - VB\)]

You can use this array-entered** formula to do that...

=MID(A1,MAX((MID(A1,ROW(1:260),1)="\")*ROW(1:260))+1,260)

** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself

Rick

 

[Ron Rosenfeld]

You can use this array-entered** formula to do that...

=MID(A1,MAX((MID(A1,ROW(1:260),1)="\")*ROW(1:260))+1,260)

** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself

Rick

Wouldn't it be better to use ROW($1:$260), so you can fill down without
changing the reference?
--ron

 

[Steven]

Thank you to you both.

You can use this array-entered** formula to do that...

=MID(A1,MAX((MID(A1,ROW(1:260),1)="\")*ROW(1:260))+1,260)

** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself

Rick

 

[Dana DeLouis]

What function would extract the name: 84DTCC 2008_05

If you would like a UDF...

Function GetFileName(strFilePath)
GetFileName = CreateObject("Scripting.FileSystemObject").GetFileName(strFilePath)
End Function

Sub TestIt()
Dim s As String
s = "G:\CorporateAcct\SMR\Special\Support\84DTCC 2008_05.pdf"
Debug.Print GetFileName(s)
End Sub

Returns:
84DTCC 2008_05.pdf

--
HTH :>)
Dana DeLouis

 

[Rick Rothstein \(MVP - VB\)]

Here is a UDF which does not rely on a scripting object (meaning it should
be a little bit faster)...

Function GetFileName(strFilePath)
GetFileName = Split(strFilePath, "\")(UBound(Split(strFilePath, "\")))
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

You can use this array-entered** formula to do that...

Wouldn't it be better to use ROW($1:$260), so you can fill down without
changing the reference?

Yes, of course. I actually had that in my original test formulas, but some
error happened in it and, after I corrected it, I didn't notice that Excel
had removed the $ signs from the equation.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Here is different version which also does not rely on a scripting object...

Function GetFileName(strFilePath)
GetFileName = Mid(strFilePath, InStrRev("\" & strFilePath, "\"))
End Function

Rick