Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
I have information in a column with a file path. I would like it to only
display the file name. Can this be done? For example: C:\test excel\PROPS\abcdefg.xls C:\test excel\PROPS\dfsdagg.xls C:\test excel\PROPS\12324vcs.xls I would like it to display only the file name without the extension or path abcdefg dfsdagg 12324vcs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Mon, 22 Oct 2007 10:58:02 -0700, BZeyger
wrote: I have information in a column with a file path. I would like it to only display the file name. Can this be done? For example: C:\test excel\PROPS\abcdefg.xls C:\test excel\PROPS\dfsdagg.xls C:\test excel\PROPS\12324vcs.xls I would like it to display only the file name without the extension or path abcdefg dfsdagg 12324vcs For a formula, assuming the file types have three characters, try: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)- FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\",""))))-4) For a UDF, which allows for file types of three or four characters, you could try this: =============================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\\,]+)\..{3,4}$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) End If End Function ================================== --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
For a UDF, which allows for file types of three or four characters, you
could try this: =============================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\\,]+)\..{3,4}$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) End If End Function ================================== Here is a UDF which works for any extension (even for a file with no extension)... Function GetFileName(FN As String) As String If Len(FN) Then GetFileName = Split(Split(FN, "\") _ (UBound(Split(FN, "\"))), ".")(0) End Function Rick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Mon, 22 Oct 2007 14:40:07 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: For a UDF, which allows for file types of three or four characters, you could try this: =============================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\\,]+)\..{3,4}$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) End If End Function ================================== Here is a UDF which works for any extension (even for a file with no extension)... Function GetFileName(FN As String) As String If Len(FN) Then GetFileName = Split(Split(FN, "\") _ (UBound(Split(FN, "\"))), ".")(0) End Function Rick That'll work, and is shorter and probably faster. I could modify mine to handle the absence of a file type extension. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
For a UDF, which allows for file types of three or four characters, you
could try this: =============================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\\,]+)\..{3,4}$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) End If End Function ================================== Here is a UDF which works for any extension (even for a file with no extension)... Function GetFileName(FN As String) As String If Len(FN) Then GetFileName = Split(Split(FN, "\") _ (UBound(Split(FN, "\"))), ".")(0) End Function Rick That'll work, and is shorter and probably faster. I don't really have a feel for the speed difference myself. Split is not the fastest function in the VB arsenol, but the of course, neither are calls to scripting engines. The String values either of our functions will have to deal are quite small, so any Split/Scripting speed differences may well be nil. It is even possible that the VBA-to-spreadsheet interface time penalty may be such as to make the Split and/or Scripting speeds appear negligiable. I could modify mine to handle the absence of a file type extension. I think you should do so. That way the OP (or others following the thread) will have a choice. Besides, if the OP (or others readers) come from a Regular Expression background, they will more than likely find your solution far more appealing than mine. Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Mon, 22 Oct 2007 16:11:43 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: I could modify mine to handle the absence of a file type extension. I think you should do so. That way the OP (or others following the thread) will have a choice. Besides, if the OP (or others readers) come from a Regular Expression background, they will more than likely find your solution far more appealing than mine. Rick To include filenames that might not include a type extension, a starting point is to modify sPat (pattern) so that the characters after the last dot "." are optional: ===================================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\\,.]+)(\.[^.]+)?$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) End If End Function ====================================== HOWEVER, this may no longer give accurate matches. Windows long file names may include the dot "." So really you'd need to exclude all possible file type extensions, but not other endings that are not. I'm no expert on allowable file type extensions in Windows. What I did below was assume that a final "." followed by three or four characters that are letters, numbers or underscore would represent a file type extension. But that other lengths or including other characters would not. It may well be that other characters should be added to the allowable list. This can be easily done. In any event, it does become a lot more complex with long filenames that might or might not include an extension: ===================================== Option Explicit Function reFilename(str As String) As String Dim re As Object Dim mc As Object Const sPat As String = "\\([^\\]+)((\.\w{3,4})|(\.[^.]*))$" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat If re.test(str) = True Then Set mc = re.Execute(str) reFilename = mc(0).submatches(0) & mc(0).submatches(3) End If End Function ========================================= It'd become even more complicated if we allowed other file systems! Best, --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I trim cell values | Excel Discussion (Misc queries) | |||
How to trim a character from a cell ? | Excel Programming | |||
Trim down on cell references | New Users to Excel | |||
Trim cell contents | Excel Worksheet Functions | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming |