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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
It's been over 20 years since I touched a Regular Expression (on a true Unix
box), so I really don't remember anything; hence, I don't know how to do this any more. However, you really only have to look at text after the last backslash, then if it has a dot in it, take only the part before it. If you can't do that in one regular expression, can't you "chain" two regular expressions together to do this? Rick "Ron Rosenfeld" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Mon, 22 Oct 2007 22:49:49 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: It's been over 20 years since I touched a Regular Expression (on a true Unix box), so I really don't remember anything; hence, I don't know how to do this any more. However, you really only have to look at text after the last backslash, then if it has a dot in it, take only the part before it. If you can't do that in one regular expression, can't you "chain" two regular expressions together to do this? That is what I did in the first pass of trying to extract file names that might or might not have a file type extension. But the following is a legitimate path name with an extension: c:\folder\book.1.xls And the following is a legitimate path name without an extension: c:\folder\book.1 And even the following, also without a file-type extension. c:\folder\book.1.part6.479001 The problem is that without some rule (3 or 4 characters) or an all-inclusive list, you really can't tell whether the characters after the last dot are a file type extension, or part of the file name. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Mon, 22 Oct 2007 23:00:59 -0400, Ron Rosenfeld
wrote: The problem is that without some rule (3 or 4 characters) or an all-inclusive list, you really can't tell whether the characters after the last dot are a file type extension, or part of the file name. For clarity, I should have added to that sentence: "...or part of the file name, if you make the file type extension optional" --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
c:\folder\book.1.xls
The problem is that without some rule (3 or 4 characters) or an all-inclusive list, you really can't tell whether the characters after the last dot are a file type extension, or part of the file name. For clarity, I should have added to that sentence: "...or part of the file name, if you make the file type extension optional" Okay, I understand what you are saying... because a filename can have a dot in it, you can't be sure if the part after a dot is part of the filename that does not have an extension or if it is the extension itself. Good point! And because of that, the function I posted earlier won't work correctly on path/filenames where the filename has a dot and an extension too (the sample filename above being an example.. my previously posted function returns "book", not "book.1"). Here is a revised function to handle that part correctly (that being, if there is a dot, the text after it is an extension which seems to be consistent with the OP's examples). Function GetFileName(FN As String) As String If Len(FN) Then GetFileName = Split(FN, "\")(UBound(Split(FN, "\"))) If InStr(GetFileName, ".") Then _ GetFileName = Left(GetFileName, InStrRev(GetFileName, ".") - 1) End If End Function Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Tue, 23 Oct 2007 04:50:11 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: c:\folder\book.1.xls The problem is that without some rule (3 or 4 characters) or an all-inclusive list, you really can't tell whether the characters after the last dot are a file type extension, or part of the file name. For clarity, I should have added to that sentence: "...or part of the file name, if you make the file type extension optional" Okay, I understand what you are saying... because a filename can have a dot in it, you can't be sure if the part after a dot is part of the filename that does not have an extension or if it is the extension itself. Good point! And because of that, the function I posted earlier won't work correctly on path/filenames where the filename has a dot and an extension too (the sample filename above being an example.. my previously posted function returns "book", not "book.1"). Here is a revised function to handle that part correctly (that being, if there is a dot, the text after it is an extension which seems to be consistent with the OP's examples). Function GetFileName(FN As String) As String If Len(FN) Then GetFileName = Split(FN, "\")(UBound(Split(FN, "\"))) If InStr(GetFileName, ".") Then _ GetFileName = Left(GetFileName, InStrRev(GetFileName, ".") - 1) End If End Function Rick The problem now, of course, goes back to your original response, where you were trying to devise a routine that would return a file name properly, even if it had no extension. For example: C:\test.excel\PROPS\abc.defgxyz.xlsx Your routine, and mine, properly return abc.defgxyz but consider: C:\test.excel\PROPS\abc.defgxyz your routine returns "abc" Mine returns "abc.defgxyz" But mine will incorrectly truncate three or four character terminations whether or not they are truly file-type extensions. I could get around that with a list of extensions to be removed in the regex. But now we are getting into an area where we need more information about the data source. A lot of this would be unnecessary if ALL of the file names have extensions. --ron |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
But now we are getting into an area where we need more information about
the data source. A lot of this would be unnecessary if ALL of the file names have extensions. You know, I was just thinking... the default in Windows XP and later versions is for Windows Explorer to "Hide extensions for known file types" unless the logged in user deliberately unchecks that option in Windows Explorer/Tools/Folder Options/View (tab)/Advanced settings (listbox) .... depending on one's level of computer sophistication, the user of your spreadsheet may not even realize that the path/filename he or she is inputting to your program even has an extension. Rick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
Here's just another idea. One has to add a little error checking though
because it will return the original string if it's not a valid path. There are a few ways to check. For here, I set a vba reference to "Microsoft Scripting Runtime" Sub Demo() Dim fn1, fn2, fn3, fn4, fn5, fn6 Dim FSO As FileSystemObject Set FSO = New FileSystemObject fn1 = "c:\folder\book.1.xls" fn2 = "c:\folder\book.1" fn3 = "c:\folder\book.1.part6.479001" fn4 = "C:\test excel\PROPS\abcdefg.xls" fn5 = "C:\test.excel\PROPS\abc.defgxyz" fn6 = "Just a String" Debug.Print FSO.GetFileName(fn1) Debug.Print FSO.GetFileName(fn2) Debug.Print FSO.GetFileName(fn3) Debug.Print FSO.GetFileName(fn4) Debug.Print FSO.GetFileName(fn5) Debug.Print FSO.GetFileName(fn6) 'fn6 returned the original string! 'One Possible Error Check: If FSO.GetDriveName(fn6) = vbNullString Then MsgBox fn6 & vbLf & "is not valid" End If End Sub -- HTH :) Dana DeLouis Windows XP & Excel 2007 "Ron Rosenfeld" wrote in message ... On Mon, 22 Oct 2007 23:00:59 -0400, Ron Rosenfeld wrote: The problem is that without some rule (3 or 4 characters) or an all-inclusive list, you really can't tell whether the characters after the last dot are a file type extension, or part of the file name. For clarity, I should have added to that sentence: "...or part of the file name, if you make the file type extension optional" --ron |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Tue, 23 Oct 2007 09:58:21 -0400, "Dana DeLouis"
wrote: Here's just another idea. One has to add a little error checking though because it will return the original string if it's not a valid path. There are a few ways to check. For here, I set a vba reference to "Microsoft Scripting Runtime" Sub Demo() Dim fn1, fn2, fn3, fn4, fn5, fn6 Dim FSO As FileSystemObject Set FSO = New FileSystemObject fn1 = "c:\folder\book.1.xls" fn2 = "c:\folder\book.1" fn3 = "c:\folder\book.1.part6.479001" fn4 = "C:\test excel\PROPS\abcdefg.xls" fn5 = "C:\test.excel\PROPS\abc.defgxyz" fn6 = "Just a String" Debug.Print FSO.GetFileName(fn1) Debug.Print FSO.GetFileName(fn2) Debug.Print FSO.GetFileName(fn3) Debug.Print FSO.GetFileName(fn4) Debug.Print FSO.GetFileName(fn5) Debug.Print FSO.GetFileName(fn6) 'fn6 returned the original string! 'One Possible Error Check: If FSO.GetDriveName(fn6) = vbNullString Then MsgBox fn6 & vbLf & "is not valid" End If End Sub The OP did NOT want the file type extension to be returned. At least when I run your routine on my machine, it does return the file type extension. --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Tue, 23 Oct 2007 09:41:37 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: But now we are getting into an area where we need more information about the data source. A lot of this would be unnecessary if ALL of the file names have extensions. You know, I was just thinking... the default in Windows XP and later versions is for Windows Explorer to "Hide extensions for known file types" unless the logged in user deliberately unchecks that option in Windows Explorer/Tools/Folder Options/View (tab)/Advanced settings (listbox) ... depending on one's level of computer sophistication, the user of your spreadsheet may not even realize that the path/filename he or she is inputting to your program even has an extension. Rick Well, the OP included extensions in his example. Just thinking about the idea of filtering out the file extensions, it would be difficult to do automatically. There is a list of *registered* file types in the Registry (over 300 in a basic windows XP installation, so I've read). But there is no requirement that all file types be registered. And I have at least one program that has unregistered file types. So I think the user would have to decide what extensions he wants to ignore; and then we could just filter those out. --ron |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
You know, I was just thinking... the default in Windows XP and later
versions is for Windows Explorer to "Hide extensions for known file types" unless the logged in user deliberately unchecks that option in Windows Explorer/Tools/Folder Options/View (tab)/Advanced settings (listbox) ... depending on one's level of computer sophistication, the user of your spreadsheet may not even realize that the path/filename he or she is inputting to your program even has an extension. Well, the OP included extensions in his example. True, but what if the OP were writing this macros for use by others and those others only knew what Windows Explorer showed them? Just thinking about the idea of filtering out the file extensions, it would be difficult to do automatically. There is a list of *registered* file types in the Registry (over 300 in a basic windows XP installation, so I've read). But there is no requirement that all file types be registered. And I have at least one program that has unregistered file types. So I think the user would have to decide what extensions he wants to ignore; and then we could just filter those out. Agreed. Otherwise, if the OP (and his/her possible user base) knew that the filenames that would be submitted to the macro always contained extensions, then the solutions we have provided should work then. Rick |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
The OP did NOT want the file type extension to be returned.
Oops! I messed that up. You're right. As an alternative, perhaps "GetBaseName" instead ??? Sub Demo() Dim f1, f2 f1 = "c:\folder\book.1.part6.479001" f2 = "c:\folder\book.1.xls" With New FileSystemObject Debug.Print .GetBaseName(f1) Debug.Print .GetBaseName(f2) End With End Sub Returns: book.1.part6 book.1 -- Dana DeLouis <snip |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to trim cell information
On Thu, 25 Oct 2007 16:22:41 -0400, "Dana DeLouis"
wrote: The OP did NOT want the file type extension to be returned. Oops! I messed that up. You're right. As an alternative, perhaps "GetBaseName" instead ??? Sub Demo() Dim f1, f2 f1 = "c:\folder\book.1.part6.479001" f2 = "c:\folder\book.1.xls" With New FileSystemObject Debug.Print .GetBaseName(f1) Debug.Print .GetBaseName(f2) End With End Sub Returns: book.1.part6 book.1 The problem now is that I don't believe that 479001 is necessarily a file type extension. So it should be included in the output. I think that if one is going to allow file names with and without extensions, the only way to exclude only extensions is to either have a list of all the extensions to be excluded, or to include some rule such as "anything with three characters; or anything with three or four characters". --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 |