Manipulating file name strings

JoeS01

Well-known Member
Joined
Jun 25, 2005
Messages
829
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
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

Well-known Member
Joined
Jun 25, 2005
Messages
829
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

Banned due to aggressive behaviour
Joined
Sep 29, 2009
Messages
172
Sorry but this question is not related to Excel in whichever means...instead, hop onto a Windows Forum.
 

JoeS01

Well-known Member
Joined
Jun 25, 2005
Messages
829

ADVERTISEMENT

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

Banned due to aggressive behaviour
Joined
Sep 29, 2009
Messages
172
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

Well-known Member
Joined
Jun 25, 2005
Messages
829

ADVERTISEMENT

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

Banned due to aggressive behaviour
Joined
Sep 29, 2009
Messages
172
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Perhaps this will help
Code:
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
 
Last edited:

JoeS01

Well-known Member
Joined
Jun 25, 2005
Messages
829
Excellent, something along the lines of your code is what I was after, thanks Mike. I have modified it to suit my specific requiremets:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,108
Messages
5,599,760
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top