Remove characters from a file name

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
98
Office Version
2019
Platform
Windows
Hello,
How to remove characters from beginning of a file in a certain folder?
I can identify the folder from a cell. (myPath="A1")
File Name (example):
07. White Knuckles.mp3
07 White Knuckles.mp3
7 White Knuckles.mp3

Maybe removing from the first space and backwards?
Thank you.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,047
Office Version
2013
Platform
Windows
If you want to rename the file, then you can do a saveas using the name with the omitted characters, then delete the old file.
If you only want to edit the text of a file name on a sheet then:

VBA Code:
Sub t()
With Range("A1")
    .Characters(1, 1).Delete
    .Characters(2, 1).Delete
End With
End Sub
 

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
98
Office Version
2019
Platform
Windows
If you want to rename the file, then you can do a saveas using the name with the omitted characters, then delete the old file.
If you only want to edit the text of a file name on a sheet then:

VBA Code:
Sub t()
With Range("A1")
    .Characters(1, 1).Delete
    .Characters(2, 1).Delete
End With
End Sub
It only using Range A1 not the entire list beginning in A1 while
removing the first 1 or 2 characters.

It's a list of file names beginning in A2 (not A1) to A???.
I would like it to begin in A2, working the range to A???
and remove all characters before and including a (space) or a (.).
Maybe looking for a (space) or a (.) in the beginning(within first 5 characters or spaces) of file (not including the extension (.)) name and delete everything
before and including the (space) and (.).
It can be 2 diff macros, if needed.
Here is what I'm working with:
Replace Files.xlsm
AB
1Old File NameNew File Name
21 Intro.mp3
302. Under And Over It.mp3
403. Burn It Down.mp3
504. American Capitalist.mp3
605. Hard To See.mp3
706. Coming Down.mp3
807. Bad Company.mp3
908. White Knuckles.mp3
1009. Drum Solo.mp3
1110. Far From Home.mp3
1211. Never Enough.mp3
1312. War Is The Answer.mp3
1413. Remember Everything.mp3
1514. No One Gets Left Behind.mp3
1615. The Bleeding.mp3
17
18
19
20
Sheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,594
Office Version
365
Platform
Windows
How about
+Fluff New.xlsm
AB
1Old File NameNew File Name
21 Intro.mp3Intro.mp3
302. Under And Over It.mp3Under And Over It.mp3
403. Burn It Down.mp3Burn It Down.mp3
504. American Capitalist.mp3American Capitalist.mp3
605. Hard To See.mp3Hard To See.mp3
706. Coming Down.mp3Coming Down.mp3
807. Bad Company.mp3Bad Company.mp3
908. White Knuckles.mp3White Knuckles.mp3
1009. Drum Solo.mp3Drum Solo.mp3
1110. Far From Home.mp3Far From Home.mp3
1211. Never Enough.mp3Never Enough.mp3
1312. War Is The Answer.mp3War Is The Answer.mp3
1413. Remember Everything.mp3Remember Everything.mp3
1514. No One Gets Left Behind.mp3No One Gets Left Behind.mp3
1615. The Bleeding.mp3The Bleeding.mp3
Results
Cell Formulas
RangeFormula
B2:B16B2=REPLACE(A2,1,FIND(" ",A2),"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
Another formula that should work...

=MID(A2,FIND(" ",A2")+1,999)
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
771
Office Version
2013
Platform
Windows
If you are looking to actually rename filenames in a particular folder (path in cell "A1" as you mentioned), you may want to use the following code. The first space and everything before it in the file name is cut out and the file is then renamed, provided the file also exists.
VBA Code:
Public Sub Example()

    Dim oWs         As Worksheet
    Dim sNameOld    As String
    Dim sPath       As String
    
    sNameOld = "07. White Knuckles.mp3"     ' <<<< change accordingly
    
    Set oWs = ActiveSheet                   ' <<<< change accordingly
    
    sPath = IIf(Right(oWs.Range("A1"), 1) = "\", oWs.Range("A1"), oWs.Range("A1") & "\")
    If Not RenameFile(sPath & sNameOld, RemovePrefix(sNameOld)) Then
        MsgBox "Source file [" & sPath & sNameOld & "] doesn't exist"
    End If
End Sub

Public Function RemovePrefix(ByVal argString As String) As String
    Dim r   As Long
    Dim s   As Long
    r = Len(argString)
    If r > 0 Then
        s = InStr(1, argString, " ", vbTextCompare)
        If Not s = 0 Then
            RemovePrefix = Mid(argString, s + 1, r - s)
        End If
    End If
End Function

Public Function RenameFile(ByVal argFullName As String, ByRef argNewName As String) As Boolean
    Dim fso     As Object
    Dim oFile   As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(argFullName) Then
        Set oFile = fso.GetFile(argFullName)
        oFile.Name = argNewName
        RenameFile = True
        Set oFile = Nothing
        Set fso = Nothing
    End If
End Function
 

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
98
Office Version
2019
Platform
Windows
How about
+Fluff New.xlsm
AB
1Old File NameNew File Name
21 Intro.mp3Intro.mp3
302. Under And Over It.mp3Under And Over It.mp3
403. Burn It Down.mp3Burn It Down.mp3
504. American Capitalist.mp3American Capitalist.mp3
605. Hard To See.mp3Hard To See.mp3
706. Coming Down.mp3Coming Down.mp3
807. Bad Company.mp3Bad Company.mp3
908. White Knuckles.mp3White Knuckles.mp3
1009. Drum Solo.mp3Drum Solo.mp3
1110. Far From Home.mp3Far From Home.mp3
1211. Never Enough.mp3Never Enough.mp3
1312. War Is The Answer.mp3War Is The Answer.mp3
1413. Remember Everything.mp3Remember Everything.mp3
1514. No One Gets Left Behind.mp3No One Gets Left Behind.mp3
1615. The Bleeding.mp3The Bleeding.mp3
Results
Cell Formulas
RangeFormula
B2:B16B2=REPLACE(A2,1,FIND(" ",A2),"")
Looks good. I will test when I get back from work.
Thank you.
 

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
98
Office Version
2019
Platform
Windows
Looks good. I will test when I get back from work.
Thank you.
Here are my results.
Replace Files.xlsm
AB
1Old File NameNew File Name
21 Intro.mp3#VALUE!
302. Under And Over It.mp3#VALUE!
403. Burn It Down.mp3#VALUE!
504. American Capitalist.mp3#VALUE!
605. Hard To See.mp3#VALUE!
706. Coming Down.mp3#VALUE!
807. Bad Company.mp3#VALUE!
908. White Knuckles.mp3#VALUE!
1009. Drum Solo.mp3#VALUE!
1110. Far From Home.mp3#VALUE!
1211. Never Enough.mp3#VALUE!
1312. War Is The Answer.mp3#VALUE!
1413. Remember Everything.mp3#VALUE!
1514. No One Gets Left Behind.mp3#VALUE!
1615. The Bleeding.mp3#VALUE!
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=REPLACE(A2,1,FIND(" ",A2),"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
Did my formula work or not? I am guessing not given the result you got from Fluff's formula. With Sheet1 active, put this formula in an empty cell and tell us what it displays...

=CODE(MID(A2,2,1))
 

Watch MrExcel Video

Forum statistics

Threads
1,102,242
Messages
5,485,607
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top