Extract file name

  • Thread starter Thread starter Steven
  • Start date Start date
S

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
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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
 
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
 
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?
Click to expand...

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
 
Here is different version which also does not rely on a scripting object...

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

Rick
 
Back
Top