If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Select specific text in cell
On Sat, 16 Feb 2008 11:09:37 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Actually, neither your one liner nor my longer variants will work if filename includes a "-". For that, we need something like: ============================== Option Explicit Function fn(str As String) As String Dim s1() As String s1 = Split(str, "\") s1 = Split(s1(UBound(s1)), "-") ReDim Preserve s1(UBound(s1) - 1) fn = Trim(Join(s1, "-")) End Function =========================== And, of course, the above would not work if the description itself contained a dash. In thinking a little more about this question, it would appear, given the structure the OP has adopted, that **at least one** of the following must be true or the OP cannot have a fool-proof parser... the filename can never have a dash, or it can never have a space/dash/space combination in it, or it can never have just a plain space in it (which would further require a space always be present after the filename), or the description cannot have a backslash in it... one of these must be true in order to create a parser (one-liner or not) that would always work. Rick Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional --ron |
#22
|
|||
|
|||
Select specific text in cell
In thinking a little more about this question, it would appear, given the
structure the OP has adopted, that **at least one** of the following must be true or the OP cannot have a fool-proof parser... the filename can never have a dash, or it can never have a space/dash/space combination in it, or it can never have just a plain space in it (which would further require a space always be present after the filename), or the description cannot have a backslash in it... one of these must be true in order to create a parser (one-liner or not) that would always work. Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional Which is, of course, a possibility; though, in today's type file referencing, somewhat rare. If the default path is at the directory where the file is located, then you can legally specify the file using something like this... c:filename.ext and the operating system will look in the current directory. On my system, there is a directory called TEMP at the root level of my C: drive. In that directory is a file named Test.txt. The following code (showing a 'backslashless' path reference) prints the first line of the file into the Immediate window... Sub test() ChDir "c:\temp" Open "c:test.txt" For Input As #1 Line Input #1, LineOfText Close #1 Debug.Print LineOfText End Sub Rick |
#23
|
|||
|
|||
Select specific text in cell
Expanding -- it would certainly be possible for a parser in which
the "\" preceding the filename was optional And I meant to my last post that doing this, or any other customization of parsing, would be fruitless without the OP coming back and filling us in on what (if any) restrictions exist on his filenames, the descriptions or the delimiter separating them. By the way, IF we had to cater to a 'backslashless' path, and IF the delimiter between the filename and description is in fact a space/dash/space, the one-liner would become slightly uglier ... Function fn(str As String) As String fn = Trim(Split(Split(Replace(str, ":", "\"), "\") _ (UBound(Split(Replace(str, ":", "\"), "\"))), " - ")(0)) End Function Rick |
#24
|
|||
|
|||
Select specific text in cell
On Sat, 16 Feb 2008 14:52:16 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional And I meant to my last post that doing this, or any other customization of parsing, would be fruitless without the OP coming back and filling us in on what (if any) restrictions exist on his filenames, the descriptions or the delimiter separating them. By the way, IF we had to cater to a 'backslashless' path, and IF the delimiter between the filename and description is in fact a space/dash/space, the one-liner would become slightly uglier ... Function fn(str As String) As String fn = Trim(Split(Split(Replace(str, ":", "\"), "\") _ (UBound(Split(Replace(str, ":", "\"), "\"))), " - ")(0)) End Function Rick Well, if the last "-" is the separator between filename and descriptor, and the "\" is optional, then the regex is simplified: re.Pattern = "([^\\]*\S)\s?-[^\\]*$" As a matter of fact, I think the only circumstance that his regex would fail would be if there were a "-" within the descriptor. --ron |
#25
|
|||
|
|||
Select specific text in cell
Expanding -- it would certainly be possible for a parser in which
the "\" preceding the filename was optional And I meant to my last post that doing this, or any other customization of parsing, would be fruitless without the OP coming back and filling us in on what (if any) restrictions exist on his filenames, the descriptions or the delimiter separating them. By the way, IF we had to cater to a 'backslashless' path, and IF the delimiter between the filename and description is in fact a space/dash/space, the one-liner would become slightly uglier ... Function fn(str As String) As String fn = Trim(Split(Split(Replace(str, ":", "\"), "\") _ (UBound(Split(Replace(str, ":", "\"), "\"))), " - ")(0)) End Function Rick Well, if the last "-" is the separator between filename and descriptor, and the "\" is optional, then the regex is simplified: re.Pattern = "([^\\]*\S)\s?-[^\\]*$" As a matter of fact, I think the only circumstance that his regex would fail would be if there were a "-" within the descriptor. Sorry, but I am not up to speed on my RegEx yet... would that retain the c: from the front of the path or not? Rick |
#26
|
|||
|
|||
Select specific text in cell
On Sat, 16 Feb 2008 14:52:16 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional And I meant to my last post that doing this, or any other customization of parsing, would be fruitless without the OP coming back and filling us in on what (if any) restrictions exist on his filenames, the descriptions or the delimiter separating them. By the way, IF we had to cater to a 'backslashless' path, and IF the delimiter between the filename and description is in fact a space/dash/space, the one-liner would become slightly uglier ... Function fn(str As String) As String fn = Trim(Split(Split(Replace(str, ":", "\"), "\") _ (UBound(Split(Replace(str, ":", "\"), "\"))), " - ")(0)) End Function Rick This, too, will work so long as there is no hyphen within description, but I couldn't make a one-liner out of it. ========================== Option Explicit Function fn(str As String) As String Dim s1() As String s1 = Split(Split(str, "\")(UBound(Split(str, "\"))), "-") ReDim Preserve s1(UBound(s1) - 1) fn = Join(s1, "-") End Function ============================== --ron |
#27
|
|||
|
|||
Select specific text in cell
On Sat, 16 Feb 2008 15:53:05 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Sorry, but I am not up to speed on my RegEx yet... would that retain the c: from the front of the path or not? If there were no "\" after the C:, then it would be retained. But if the C: represents a drive, is C:filename legal? OR must it be C:\filename For the latter, the C:\ would NOT be returned. --ron |
#28
|
|||
|
|||
Select specific text in cell
This, too, will work so long as there is no hyphen within description, but
I couldn't make a one-liner out of it. ========================== Option Explicit Function fn(str As String) As String Dim s1() As String s1 = Split(Split(str, "\")(UBound(Split(str, "\"))), "-") ReDim Preserve s1(UBound(s1) - 1) fn = Join(s1, "-") End Function ============================== I think this will work under those conditions... Function fn(str As String) As String fn = Trim$(Split(Left(str, InStrRev(str, "-") - 1), _ "\")(UBound(Split(str, "\")))) End Function Note that the Trim function call is needed if we are not sure whether the filename/description delimiter is always a space/dash/space. If that is always the delimiter, then the function can be simplified to this... Function fn(str As String) As String fn = Split(Left(str, InStrRev(str, "-") - 2), _ "\")(UBound(Split(str, "\"))) End Function By the way, I estimate that statement line is 3 characters too long to fit on one, non-continued line before newsreader line wrapping would mangle it; hence, the line continuation. For example, if we reduce the argument name to just S, then the function is this neater looking one... Function fn(S As String) As String fn = Split(Left(S, InStrRev(S, "-") - 2), "\")(UBound(Split(S, "\"))) End Function Rick |
#29
|
|||
|
|||
Select specific text in cell
Sorry, but I am not up to speed on my RegEx yet... would that retain the
c: from the front of the path or not? If there were no "\" after the C:, then it would be retained. But if the C: represents a drive, is C:filename legal? OR must it be C:\filename Yes, c:filename is legal. In the flurry of postings we have done, you missed my 2:40PM (EST) message. I am repeating it here so you don't have to look for it... see my test() subroutine and note in particular the path/filename used in the Open statement. Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional Which is, of course, a possibility; though, in today's type file referencing, somewhat rare. If the default path is at the directory where the file is located, then you can legally specify the file using something like this... c:filename.ext and the operating system will look in the current directory. On my system, there is a directory called TEMP at the root level of my C: drive. In that directory is a file named Test.txt. The following code (showing a 'backslashless' path reference) prints the first line of the file into the Immediate window... Sub test() ChDir "c:\temp" Open "c:test.txt" For Input As #1 Line Input #1, LineOfText Close #1 Debug.Print LineOfText End Sub Rick |
#30
|
|||
|
|||
Select specific text in cell
On Sat, 16 Feb 2008 16:31:39 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Sorry, but I am not up to speed on my RegEx yet... would that retain the c: from the front of the path or not? If there were no "\" after the C:, then it would be retained. But if the C: represents a drive, is C:filename legal? OR must it be C:\filename Yes, c:filename is legal. In the flurry of postings we have done, you missed my 2:40PM (EST) message. I am repeating it here so you don't have to look for it... see my test() subroutine and note in particular the path/filename used in the Open statement. Expanding -- it would certainly be possible for a parser in which the "\" preceding the filename was optional Which is, of course, a possibility; though, in today's type file referencing, somewhat rare. If the default path is at the directory where the file is located, then you can legally specify the file using something like this... c:filename.ext and the operating system will look in the current directory. On my system, there is a directory called TEMP at the root level of my C: drive. In that directory is a file named Test.txt. The following code (showing a 'backslashless' path reference) prints the first line of the file into the Immediate window... Sub test() ChDir "c:\temp" Open "c:test.txt" For Input As #1 Line Input #1, LineOfText Close #1 Debug.Print LineOfText End Sub Rick Well, as written, the regex would retain the C:. If you wanted to return filename without the C:, it would be a simple alteration in the regex: re.Pattern = "([^\\:]*\S)\s?-[^\\]*$" This is a bit more robust, though: re.Pattern = "([^\\:]*\S)\s*-[^\\]*$" --ron |
Thread Tools | |
Display Modes | |
|
|