Extract file name
 

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

Extract file name
 

On Fri, 8 Aug 2008 16:23:00 -0700, Steven
wrote:

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

Extract file name
 

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


"Steven" wrote in message
...
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

Extract file name
 

On Fri, 8 Aug 2008 20:30:13 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Extract file name
 

Thank you to you both.

"Rick Rothstein (MVP - VB)" wrote:

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


"Steven" wrote in message
...
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

Extract file name
 

What function would extract the name: 84DTCC 2008_05

If you would like a UDF...

Function GetFileName(strFilePath)
GetFileName = CreateObject("Scripting.FileSystemObject").GetFile Name(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


"Steven" wrote in message ...

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

Extract file name
 

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


"Dana DeLouis" wrote in message
...
What function would extract the name: 84DTCC 2008_05

If you would like a UDF...

Function GetFileName(strFilePath)
GetFileName =
CreateObject("Scripting.FileSystemObject").GetFile Name(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


"Steven" wrote in message
...
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

Extract file name
 

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?

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

Extract file name
 

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

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Dana DeLouis" wrote in message
...
What function would extract the name: 84DTCC 2008_05

If you would like a UDF...

Function GetFileName(strFilePath)
GetFileName =
CreateObject("Scripting.FileSystemObject").GetFile Name(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


"Steven" wrote in message
...
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