Add File Name to a Cell without Extension?

[slam]

Hi all,

I've seen a few ways to do this, but they don't account for file extensions being different lengths - typically 3 (.xls) or 4 (.xlms)

Is there anyway this can be done? My file names will never have a period in them if that helps.

Thanks

 

[JoeMo]

Hi all,

I've seen a few ways to do this, but they don't account for file extensions being different lengths - typically 3 (.xls) or 4 (.xlms)

Is there anyway this can be done? My file names will never have a period in them if that helps.

Thanks

In any empty cell in any worksheet in your file enter:

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))

 

[slam]

Thank you so much! Curious - what does the A1 reference actually do? Doesn't seem to have any impact on the result?

 

[Firefly2012]

Here's a particularly gruesome formula you could use (there may well be simpler formulas possible):

Excel 2010
	A
6	C:\Users\Mini-ITX\Documents\MrExcel\[Write_Old_Text_Files_To_Output_Text_File.xlsm]Sheet1
7	Write_Old_Text_Files_To_Output_Text_File
Sheet1

Cell Formulas
Range	Formula
A6	=CELL("filename",A1)
A7	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(CELL("filename",A1),"[",REPT(" ",500)),500,500)),".",REPT(" ",100)),100))

 

[JoeMo]

Thank you so much! Curious - what does the A1 reference actually do? Doesn't seem to have any impact on the result?

A1 is simply a reference to a cell on the sheet that contains the formula, it can be any cell on the sheet, but if omitted the default will be the last cell that was changed. In your case, if that cell happened to be in another file you would not get the result you want so best not to omit it. By default, I always use A1.

 

[PBWood]

Sorry to drag this back up.

I'm trying to use this formula but it looks like my server doesn't include the '[]' at either end of the filename, and so the formula fails. This is using Excel 2016 and saving the files as .csv

Is there something obvious I'm missing?

Thanks!

 

[JoeMo]

Sorry to drag this back up.

I'm trying to use this formula but it looks like my server doesn't include the '[]' at either end of the filename, and so the formula fails. This is using Excel 2016 and saving the files as .csv

Is there something obvious I'm missing?

Thanks!

What does the formula: =CELL("filename",A1) return?

 

[PBWood]

What does the formula: =CELL("filename",A1) return?

I'll give a bit of background in the hope that this might help...

I am creating templates to use for uploading data into our database, and the templates are saved as .xlsx format. Before uploading to the database, they need to be saved as a .csv file. When in .xlsx format, the formula works and the formula you've mentioned shows a filename with '[]' in. When saving as a .csv, the '[]' disappear and so the original formula errors.

So, it looks like I was wrong with my original idea that it was something to do with the server and it's actually more to do with the file format. Is there a way round this for .csv files?

 

[JoeMo]

I'll give a bit of background in the hope that this might help...

I am creating templates to use for uploading data into our database, and the templates are saved as .xlsx format. Before uploading to the database, they need to be saved as a .csv file. When in .xlsx format, the formula works and the formula you've mentioned shows a filename with '[]' in. When saving as a .csv, the '[]' disappear and so the original formula errors.

So, it looks like I was wrong with my original idea that it was something to do with the server and it's actually more to do with the file format. Is there a way round this for .csv files?

This should work for your csv file, but could probably be done in a way that requires far fewer function calls. It's just the first apporach that comes to mind for me.

=MID(SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),SEARCH("[",SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,SEARCH(".",CELL("filename",A1))-SEARCH("[",SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-1)

Basically, this substitutes "[" for the last backslash character that comes before the file name, and uses that marker and the "." before the csv extension to isolate just the file name.

 

[PBWood]

This should work for your csv file, but could probably be done in a way that requires far fewer function calls. It's just the first apporach that comes to mind for me.

=MID(SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))),SEARCH("[",SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))+1,SEARCH(".",CELL("filename",A1))-SEARCH("[",SUBSTITUTE(CELL("filename",A1),"\","[",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))))-1)

Basically, this substitutes "[" for the last backslash character that comes before the file name, and uses that marker and the "." before the csv extension to isolate just the file name.

Thanks very much, I'll give this a go!