ExcelBanter - Select specific text in cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Select specific text in cell (https://www.excelbanter.com/excel-discussion-misc-queries/176898-select-specific-text-cell.html)

John Gregory

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

Max

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

FSt1

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

Ron Rosenfeld

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

John Gregory

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

Ron Rosenfeld

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

FSt1

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

Ron Rosenfeld

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

Ron Rosenfeld

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

John Gregory

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

Ron Rosenfeld

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

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_32_]

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

Ron Rosenfeld

Select specific text in cell

Ron Rosenfeld

Select specific text in cell

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_35_]

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

Rick Rothstein $MVP - VB$[_36_]

Select specific text in cell

Rick Rothstein $MVP - VB$[_37_]

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

Ron Rosenfeld

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

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_38_]

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

Rick Rothstein $MVP - VB$[_39_]

Select specific text in cell

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_40_]

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

Ron Rosenfeld

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

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_41_]

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

Rick Rothstein $MVP - VB$[_42_]

Select specific text in cell

Ron Rosenfeld

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

Rick Rothstein $MVP - VB$[_43_]

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

All times are GMT +1. The time now is 02:53 PM.