| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vba code for excel to extract data from txt file | New Users to Excel | |||
| Can I extract a picture from an excel file and save it elsewhere? | Excel Discussion (Misc queries) | |||
| Extract from a file | Excel Discussion (Misc queries) | |||
| How to extract value from a different file? | Excel Worksheet Functions | |||
| Macro to extract clip-art file? | Excel Discussion (Misc queries) | |||
Linear Mode
