Returning Part of a String

[lecoughlin]

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]

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

 

[Gary''s Student]

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

 

[Rick Rothstein]

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

 

[Harlan Grove]

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

 

[Rick Rothstein]

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 Rothstein]

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

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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!

 

[Ron Rosenfeld]

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.

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

Thanks
--ron