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

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

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

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

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

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

"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

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

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

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