Manipulating file name strings

[JoeS01]

I have a filename in the format XXX-YYY-ZZZ.xls, where all the XXX and YYY and ZZZ are of variable length. Sometimes it is of the format UUU-XXX-YYY-ZZZZ.xls

I wish to delete all the characters to the left of the rightmost "-" character, including that "-" character.

Can anyone please advise which VBA string manipulation function will best do this?

regards,
Joe

 

[Peter_SSs]

Joe

I think you've still got more in there than you need. Have a look at these two and see if they still do what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ChangeFileName1()<br>    <SPAN style="color:#00007F">Dim</SPAN> strInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> RemoveMinus$<br>    <SPAN style="color:#00007F">Dim</SPAN> workRRay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br> <br>    strInput = "XXX-YYY-UUUU.xls"<br>    workRRay = Split(strInput, "-")<br>    RemoveMinus = workRRay(UBound(workRRay))<br>    MsgBox RemoveMinus<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ChangeFileName2()<br>    <SPAN style="color:#00007F">Dim</SPAN> strInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> RemoveMinus$<br>    <br>    strInput = "XXX-YYY-UUUU.xls"<br>    RemoveMinus = Right(strInput, Len(strInput) - InStrRev(strInput, "-"))<br>    MsgBox RemoveMinus<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JoeS01]

Thanks Peter and Mike. I like both ways, they both do the job quite well, but Mike's is preferred because the Split function I have used many times before, an dit is easy to understand ( I am used to working with arrays).

I have a bit of trouble understanding how Peter's method works, not being right up on string manipulation

regards,
Joe

 

[Peter_SSs]

Thanks Peter and Mike. I like both ways, they both do the job quite well, but Mike's is preferred because the Split function I have used many times before, an dit is easy to understand ( I am used to working with arrays).

I have a bit of trouble understanding how Peter's method works, not being right up on string manipulation

regards,
Joe

I'm not sure if you understood that I was suggesting two different ways, not one way with two components.

You had indicated that you modified Mike's code to suit your purposes. My first set of code was just a simplification of your code, still using the Split function. I was indicating ...

1. That you didn't need a separate test to see if there was no "-" chaarcters in the string, and

2. That you didn't need any looping (GoTo Test) because the result could be achieved with just one pass of the code every time.

Anyway, it doesn't really matter so long as you have a method that is working and you are happy with it.