Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
Is there a simple way to extract text in a cell that occurs between specific
characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
Using MID comes to mind
Post some examples for better answers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "John Gregory" wrote in message ... Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
hi
you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 16:20:00 -0800, John Gregory
wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 Yes --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
The text strings are random length - file path names:
c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas "FSt1" wrote: hi you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory
wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by <space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR (1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
hi,
using your example as an example, try this.... =MID(C1,(SEARCH("\",C1,4)+1),(SEARCH("-",C1,4)-1)-SEARCH("\",C1,4)) returns "filename345" look up =search in xl help also. Regards FSt1 "John Gregory" wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas "FSt1" wrote: hi you didn't specify any thing specific so all i can say is to look up these function in xl help =left......returns any number of characters that you specify starting from the left =right....same as =left except starts from the right =mid.....returns any number of characters that you specify starting from a point within the text to another point within the text regards FSt1 "John Gregory" wrote: Is there a simple way to extract text in a cell that occurs between specific characters, or to the right of a special character? The text strings are various lengths and content. Excel 2003, Windows XP SP2 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 21:36:13 -0500, Ron Rosenfeld
wrote: On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by <space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHA R(1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron By the way, here are some UDF's that will do the same thing. They can be entered in a regular module and then used as a function. To enter into a regular module, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste one of the codes below into the window that opens: ============================================= Option Explicit Function fn(str As String) As String Dim s1() As String Dim s2() As String s1 = Split(str, "\") s2 = Split(s1(UBound(s1)), "-") fn = Trim(s2(LBound(s2))) End Function ========================================== The above as a "one-liner" in deference to Rick: ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== and using Regular Expressions, which, although a bit longer, took a fraction of the time to develop and test of any of the other solutions. ================================ Option Explicit Function fn(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\\([^\-\\]*\S)\s?-[^\\]*$" If re.test(str) = True Then Set mc = re.Execute(str) fn = mc(0).submatches(0) End If End Function ================================= --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 18:40:00 -0800, FSt1
wrote: hi, using your example as an example, try this.... =MID(C1,(SEARCH("\",C1,4)+1),(SEARCH("-",C1,4)-1)-SEARCH("\",C1,4)) returns "filename345" look up =search in xl help also. Regards FSt1 Your routine will only return just the filename only if filename is in a folder in the root directory; and also if there are no "-" in the folder or filenames. For example: c:\sampledir1\subfolder1\filename - description your formula returns: subfolder1\filename OR c:\samplelongname2\filename-345 - longer description where it will only return filename and not filename-345 --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
That works! Thank you very much
"Ron Rosenfeld" wrote: On Fri, 15 Feb 2008 18:07:05 -0800, John Gregory wrote: The text strings are random length - file path names: c:\sampledir1\filename - description In this example, I want to get the string "filename", but I have many directories, all with differnt length names. c:\samplelongname2\filename345 - longer description etc. In all cases I want to get the text between the "/" and the "-". The right and left functions do not work because the number of characters varies. Any ideas In your example, the - is surrounded by <space on both sides. If this is the case in your strings, it would be more robust to look for that sequence, than just for the "-". With your string in A1, here is a formula that will extract the string that is between the last "\" and the last "-" : =TRIM(MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR (1), LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),-1+FIND(CHAR(1), SUBSTITUTE(A1,"-",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) -FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)- LEN(SUBSTITUTE(A1,"\","")))))) --ron |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 22:00:45 -0500, Ron Rosenfeld
wrote: and using Regular Expressions, which, although a bit longer, took a fraction of the time to develop and test of any of the other solutions. ================================ Option Explicit Function fn(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\\([^\-\\]*\S)\s?-[^\\]*$" If re.test(str) = True Then Set mc = re.Execute(str) fn = mc(0).submatches(0) End If End Function ================================= OF course, the regular expression variation as posted above is wrong <g. Should read: ==================================== Option Explicit Function fn(str As String) As String Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\\([^\\]*\S)\s?-[^\\]*$" If re.test(str) = True Then Set mc = re.Execute(str) fn = mc(0).submatches(0) End If End Function ===================================== --ron |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 19:14:01 -0800, John Gregory
wrote: That works! Thank you very much You're welcome. Thanks for the feedback. --ron |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
The above as a "one-liner" in deference to Rick:
LOL ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== The LBound for a Split is always 0 no matter what the Option Base is set to. Using this fact, your one-liner can be simplified considerably... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0)) End Function Rick |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 23:00:40 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: The above as a "one-liner" in deference to Rick: LOL ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== The LBound for a Split is always 0 no matter what the Option Base is set to. Using this fact, your one-liner can be simplified considerably... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0)) End Function Nice --ron |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 23:00:40 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: The above as a "one-liner" in deference to Rick: LOL ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== The LBound for a Split is always 0 no matter what the Option Base is set to. Using this fact, your one-liner can be simplified considerably... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0)) End Function And you've told me that before <grrr <slap upside my head --ron |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
On Fri, 15 Feb 2008 23:00:40 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: The above as a "one-liner" in deference to Rick: LOL ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== The LBound for a Split is always 0 no matter what the Option Base is set to. Using this fact, your one-liner can be simplified considerably... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0)) End Function Rick 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 =========================== --ron |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
"Ron Rosenfeld" wrote in message ... On Fri, 15 Feb 2008 23:00:40 -0500, "Rick Rothstein \(MVP - VB\)" wrote: The above as a "one-liner" in deference to Rick: LOL ============================================ Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split _ (str, "\"))), "-")(LBound(Split(Split _ (str, "\")(UBound(Split(str, "\"))), "-")))) End Function ========================================== The LBound for a Split is always 0 no matter what the Option Base is set to. Using this fact, your one-liner can be simplified considerably... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), "-")(0)) End Function Rick 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 =========================== I thought I had corrected it as per your observation after the OP posted his sample text line ... use " - ", not "-", in the one-liner... Function fn(str As String) As String fn = Trim(Split(Split(str, "\")(UBound(Split(str, "\"))), " - ")(0)) End Function Of course, this supposes the filename itself does not contain dash surrounded by spaces. Rick |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
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. Rick |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select specific text in cell
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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 That sounds correct. And, at least for the parser's I've offered, the filename must be preceded by a "\" --ron |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#31
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 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*-[^\\]*$" I wouldn't worry about it. Even though it is legal to have a path with no backslashes, almost no one does so anymore as it requires some mechanism to change the active directory path in order to use it. One would hardly store paths in that format with the requirement that to use them, a certain path has to be made active. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF a cell contains specific text | Excel Discussion (Misc queries) | |||
cell location containing specific text | Excel Worksheet Functions | |||
Macro - Select cell with no text | Excel Worksheet Functions | |||
Select cell containing specific text &return value from another ce | Excel Worksheet Functions | |||
Select specific cell | Excel Discussion (Misc queries) |