Remove characters from a file name

[gtd526]

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.

 

[JLGWhiz]

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:

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

 

[gtd526]

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:

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
	A	B
1	Old File Name	New File Name
2	1 Intro.mp3
3	02. Under And Over It.mp3
4	03. Burn It Down.mp3
5	04. American Capitalist.mp3
6	05. Hard To See.mp3
7	06. Coming Down.mp3
8	07. Bad Company.mp3
9	08. White Knuckles.mp3
10	09. Drum Solo.mp3
11	10. Far From Home.mp3
12	11. Never Enough.mp3
13	12. War Is The Answer.mp3
14	13. Remember Everything.mp3
15	14. No One Gets Left Behind.mp3
16	15. The Bleeding.mp3
17
18
19
20
Sheet1

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1	Old File Name	New File Name
2	1 Intro.mp3	Intro.mp3
3	02. Under And Over It.mp3	Under And Over It.mp3
4	03. Burn It Down.mp3	Burn It Down.mp3
5	04. American Capitalist.mp3	American Capitalist.mp3
6	05. Hard To See.mp3	Hard To See.mp3
7	06. Coming Down.mp3	Coming Down.mp3
8	07. Bad Company.mp3	Bad Company.mp3
9	08. White Knuckles.mp3	White Knuckles.mp3
10	09. Drum Solo.mp3	Drum Solo.mp3
11	10. Far From Home.mp3	Far From Home.mp3
12	11. Never Enough.mp3	Never Enough.mp3
13	12. War Is The Answer.mp3	War Is The Answer.mp3
14	13. Remember Everything.mp3	Remember Everything.mp3
15	14. No One Gets Left Behind.mp3	No One Gets Left Behind.mp3
16	15. The Bleeding.mp3	The Bleeding.mp3
Results

Cell Formulas
Range	Formula
B2:B16	B2	=REPLACE(A2,1,FIND(" ",A2),"")

 

[Rick Rothstein]

Another formula that should work...

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

 

[GWteB]

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.

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]

How about

+Fluff New.xlsm
	A	B
1	Old File Name	New File Name
2	1 Intro.mp3	Intro.mp3
3	02. Under And Over It.mp3	Under And Over It.mp3
4	03. Burn It Down.mp3	Burn It Down.mp3
5	04. American Capitalist.mp3	American Capitalist.mp3
6	05. Hard To See.mp3	Hard To See.mp3
7	06. Coming Down.mp3	Coming Down.mp3
8	07. Bad Company.mp3	Bad Company.mp3
9	08. White Knuckles.mp3	White Knuckles.mp3
10	09. Drum Solo.mp3	Drum Solo.mp3
11	10. Far From Home.mp3	Far From Home.mp3
12	11. Never Enough.mp3	Never Enough.mp3
13	12. War Is The Answer.mp3	War Is The Answer.mp3
14	13. Remember Everything.mp3	Remember Everything.mp3
15	14. No One Gets Left Behind.mp3	No One Gets Left Behind.mp3
16	15. The Bleeding.mp3	The Bleeding.mp3
Results

Cell Formulas
Range	Formula
B2:B16	B2	=REPLACE(A2,1,FIND(" ",A2),"")

Looks good. I will test when I get back from work.
Thank you.

 

[gtd526]

Another formula that should work...

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

Thank you.

 

[gtd526]

Looks good. I will test when I get back from work.
Thank you.

Here are my results.

Replace Files.xlsm
	A	B
1	Old File Name	New File Name
2	1 Intro.mp3	#VALUE!
3	02. Under And Over It.mp3	#VALUE!
4	03. Burn It Down.mp3	#VALUE!
5	04. American Capitalist.mp3	#VALUE!
6	05. Hard To See.mp3	#VALUE!
7	06. Coming Down.mp3	#VALUE!
8	07. Bad Company.mp3	#VALUE!
9	08. White Knuckles.mp3	#VALUE!
10	09. Drum Solo.mp3	#VALUE!
11	10. Far From Home.mp3	#VALUE!
12	11. Never Enough.mp3	#VALUE!
13	12. War Is The Answer.mp3	#VALUE!
14	13. Remember Everything.mp3	#VALUE!
15	14. No One Gets Left Behind.mp3	#VALUE!
16	15. The Bleeding.mp3	#VALUE!
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=REPLACE(A2,1,FIND(" ",A2),"")

 

[Rick Rothstein]

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))