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.

 

[Tom.Jones]

Another formula that should work...

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

It's a typo in Rick's formula, must be:
=MID(A2,FIND(" ",A2)+1,999)

 

[Fluff]

You could try
=REPLACE(A2,1,FIND(CHAR(160),A2),"")
If that doesn't work you will need to let us know what Rick's formula in post#10 returns.

 

[gtd526]

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

The first formula works, the second returns numbers. Here are the results.

Replace Files.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	55
9	08. White Knuckles.mp3	56
10	09. Drum Solo.mp3	57
11	10. Far From Home.mp3	48
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:B7	B2	=MID(A2,FIND(" ",A2)+1,999)
B8:B11	B8	=CODE(MID(A8,2,1))
B12:B16	B12	=REPLACE(A12,1,FIND(" ",A12),"")

 

[gtd526]

Here are the results of 4 formulas:

Replace Files.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	55
9	08. White Knuckles.mp3	56
10	09. Drum Solo.mp3	57
11	10. Far From Home.mp3	48
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:B7	B2	=MID(A2,FIND(" ",A2)+1,999)
B8:B11	B8	=CODE(MID(A8,2,1))
B12:B14	B12	=REPLACE(A12,1,FIND(CHAR(160),A12),"")
B15:B16	B15	=REPLACE(A15,1,FIND(" ",A15),"")

 

[Fluff]

If Rick's formula works, then use that.
Although I don't understand how his works & mine doesn't.

 

[gtd526]

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

I'm trying to put this formula in a macro but receive this error:

 

[gtd526]

If Rick's formula works, then use that.
Although I don't understand how his works & mine doesn't.

Thank you.

 

[Fluff]

You need to double-up the quotes inside the formula like
"=MID(A2,FIND("" "",A2)+1,999)"

 

[gtd526]

You need to double-up the quotes inside the formula like
"=MID(A2,FIND("" "",A2)+1,999)"

Yes. It works. Thank you.

 

[gtd526]

Yes. It works. Thank you.

Here are the results:

Replace Files.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
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=MID(A2,FIND(" ",A2)+1,999)