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

 

[jbeaucaire]

Are you asking a worksheet cell formula question? These are text strings in cells you want to change?

Or are you asking how to rename files on the hard drive by removing everything prior to and including the last "-" in the actual filename?

 

[JoeS01]

Or are you asking how to rename files on the hard drive by removing everything prior to and including the last "-" in the actual filename?

Yes, this is what I am trying to do. Sorry for any confusion

regards,
Joe

 

[Nimit]

Sorry but this question is not related to Excel in whichever means...instead, hop onto a Windows Forum.

 

[JoeS01]

Nimit, you are incorrect, very much so. This is a VBA Excel task, I am writing in VBA, and is a small Function that I am writing as part of a larger VBA script. I need to clean up file names first.

I was hoping you might be able to help

regards,
JoeS

 

[Nimit]

Do you have all the filenames listed in a range of cells on any of your worksheets which you want to change as per your requirements mentioned above ?

 

[JoeS01]

Nimit, there is no need for me to create a range of filenames in a worksheet at this stage, although I can easily do that.

All I want to do is cycle through the files in a directory that are sent to me by different people, usually prefiexed by their initials and location, grab each file name, modify it by removing the prefixes, and rename the file with this modification.

That is not a problem, once I get the modifying part right.

The problem I have is in manipulating a string of characters ( ie , the file name) - a simple VBA string thing, I would have thought, but it has me beat. Hence the request for help from this forum.

regards,
Joe

 

[Nimit]

Could this help you ?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=827

Also, I asked that question because I could write a program which would fetch the filenames from a particular directory to your excel worksheet. Once you do this, you can then modify that column list in another column using a simple formula. Finally, you can use another program for copying the modified filenames to rename all these files in that directory (I had encountered that type of a program a couple of years back on this board but I have lost all my subscribed threads due to my stupidity and so I cannot fetch that program for you unless you do a search).

 

[mikerickson]

Perhaps this will help

Sub test()
    Dim pathString As String
    Dim workRRay As Variant
    
    pathString = Application.GetOpenFilename
    If pathString = "False" Then Exit Sub: Rem cancel pressed
    
    workRRay = Split(pathString, "-")
    ReDim Preserve workRRay(0 To UBound(workRRay) - 1)

    Name pathString As Join(workRRay, "-") & ".xls"
End Sub

 

[JoeS01]

Excellent, something along the lines of your code is what I was after, thanks Mike. I have modified it to suit my specific requiremets:

Sub ChangeFileName()
    Dim strInput As String
    Dim RemoveMinus$
    Dim workRRay As Variant
 
    strInput = "XXX-YYY-UUUU.xls"
 
Test:
    If InStr(strInput, "-") = 0 Then
        ' no "-" characters
          RemoveMinus = strInput
    Else
        workRRay = Split(strInput, "-")
 
        strInput = workRRay(UBound(workRRay))
        GoTo Test
    End If
 
    MsgBox (RemoveMinus)
 
End Sub