I have the a set of strings - file paths - that follow this general
format:

C:\My Documents\Tools\New Hampshire_1.xls
C:\My Documents\Tools\New Hampshire_11.xls
C:\My Documents\Tools\Vermont_2.xls

So basically the state, underscore, and number.

What code could I write to create a new string with only the New
Hampshire_1 part?

Thanks in advance.

On Fri, 24 Oct 2008 10:17:14 -0700 (PDT), wrote:

I have the a set of strings - file paths - that follow this general
format:

C:\My Documents\Tools\New Hampshire_1.xls
C:\My Documents\Tools\New Hampshire_11.xls
C:\My Documents\Tools\Vermont_2.xls

So basically the state, underscore, and number.

What code could I write to create a new string with only the New
Hampshire_1 part?

Thanks in advance.

====================
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function
============================
--ron

Sub dural()
s = "C:\My Documents\Tools\New Hampshire_1.xls"
ar = Split(s, "\")
s1 = ar(UBound(ar))
s2 = Left(s1, Len(s1) - 4)
MsgBox (s2)
End Sub

--
Gary''s Student - gsnu200809

Here is one more method for you to consider...

Function FileName(FN As String) As String
FileName = Mid(Left(FN, InStrRev(FN, ".") - 1), InStrRev(FN, "\") + 1)
End Function

--
Rick (MVP - Excel)


wrote in message
...
I have the a set of strings - file paths - that follow this general
format:

C:\My Documents\Tools\New Hampshire_1.xls
C:\My Documents\Tools\New Hampshire_11.xls
C:\My Documents\Tools\Vermont_2.xls

So basically the state, underscore, and number.

What code could I write to create a new string with only the New
Hampshire_1 part?

Thanks in advance.

Ron Rosenfeld wrote...
....
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q < p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

If you order the tests properly, you can combine all this into a one-liner
(see my post from about an hour ago) that doesn't need to test the positions
the way your code does.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Ron Rosenfeld wrote...
...
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q < p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

Sorry... cancel that... my function doesn't work if there isn't a
dot-extension.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If you order the tests properly, you can combine all this into a one-liner
(see my post from about an hour ago) that doesn't need to test the
positions the way your code does.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Ron Rosenfeld wrote...
...
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q < p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Just to point out, your function will return an incorrect result if there is
no extension provided and the filename has a dot in it. However, I'm not to
sure this is a situation that can reliably be protected against.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Ron Rosenfeld wrote...
...
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q < p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

On Fri, 24 Oct 2008 11:49:02 -0700 (PDT), Harlan Grove
wrote:

This works, but using Split is somewhat wasteful.

Why is Split wasteful?

And is there someplace I can read about this issue (wastefulness) in general?
--ron

Okay, determined to produce a "reasonable" one-liner that works to replace
the one-liner I posted previously, here is that effort (it appears to return
the same values as your function)...

Function FileName(FN As String) As String
FileName = Replace(Replace(FN, "\", "", InStrRev(FN, "\")), _
Mid(FN, InStrRev(FN, ".")), "")
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Sorry... cancel that... my function doesn't work if there isn't a
dot-extension.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If you order the tests properly, you can combine all this into a
one-liner (see my post from about an hour ago) that doesn't need to test
the positions the way your code does.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
Ron Rosenfeld wrote...
...
Function fn(str As String) As String
Dim sTemp
sTemp = Split(str, "\")
fn = sTemp(UBound(sTemp))
fn = Left(fn, InStrRev(fn, ".") - 1)
End Function

This works, but using Split is somewhat wasteful. Also doesn't hurt to
add error checking in case the last token doesn't contain a period.

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q < p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

Ron Rosenfeld wrote...
....
Why is Split wasteful?

It does more than necessary. It locates every \ and creates an array,
neither of which are necessary, both of which require execution time
and additional memory usage.

And is there someplace I can read about this issue (wastefulness) in general?

Benchamarking, both for speed and resources use. Web search on
programming and either benchmarking, efficiency or performance.

"Rick Rothstein" wrote...
....
Just to point out, your function will return an incorrect result if there is
no extension provided and the filename has a dot in it. However, I'm not to
sure this is a situation that can reliably be protected against.

You mean Unix-like filenames like .profile? You have a point. Could be
handled by

Function basename(s As String) As String
Dim p As Long, q As Long
p = InStrRev(s, "\") + 1
q = InStrRev(s, ".")
If q <= p Then q = Len(s) + 1
basename = Mid(s, p, q - p)
End Function

In which case a comparison of our revised udfs gives

Pathname RR udf HG udf

Pathname RR udf HG udf
a:\b\c\d.e d d
a:\b\c.d.e c.d c.d
a:\b\c.d\e e e
a:\b\c\d\e #VALUE! e
a:\b\c\d\.e .e
xxxxxxxx #VALUE! xxxxxxxx
xxxxxxxx. #VALUE! xxxxxxxx
..xxxxxxxx #VALUE! .xxxxxxxx
\xxxxxxxx #VALUE! xxxxxxxx
xxxxxxxx\ #VALUE!

On Sat, 25 Oct 2008 11:21:55 -0700 (PDT), Harlan Grove
wrote:

Ron Rosenfeld wrote...
...
Why is Split wasteful?

It does more than necessary. It locates every \ and creates an array,
neither of which are necessary, both of which require execution time
and additional memory usage.

That makes sense. And in running a timing routine on both, the Split seems to
take orders of magnitude longer than your routine.


And is there someplace I can read about this issue (wastefulness) in general?

Benchamarking, both for speed and resources use. Web search on
programming and either benchmarking, efficiency or performance.

Thanks
--ron