Returning Part of a String
 

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.

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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.

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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

Returning Part of a String
 

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.

Returning Part of a String
 

"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!

Returning Part of a String
 

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