File name without displaying the full path
yomosaique
-
Mike-31 Posted messages 18405 Registration date Status Contributeur Last intervention -
Mike-31 Posted messages 18405 Registration date Status Contributeur Last intervention -
Hello,
I want to display the filename in a cell. I have noted the formula ""=CELL("filename")"" but is it because I'm working on a server? I get the full path displayed, for example =\\server2019\01_affaires\etu_pro\01_com\outil_commercial\[devis_DE02161.xlsx]Recap
The name of the sheet where my cell is located also appears in this path... This is of no use to me; I only want to show the filename ideally without the .xlsx extension, so just DE02161. Thank you for your help
Configuration: Windows / Firefox 70.0
I want to display the filename in a cell. I have noted the formula ""=CELL("filename")"" but is it because I'm working on a server? I get the full path displayed, for example =\\server2019\01_affaires\etu_pro\01_com\outil_commercial\[devis_DE02161.xlsx]Recap
The name of the sheet where my cell is located also appears in this path... This is of no use to me; I only want to show the filename ideally without the .xlsx extension, so just DE02161. Thank you for your help
Configuration: Windows / Firefox 70.0
2 réponses
Re,
There are old XL4 macro syntaxes like READ.CELL for the curious; I detailed some possibilities at this link
https://www.commentcamarche.net/applis-sites/bureautique/1521-compter-ou-additionner-des-cellules-colorisees-manuellement-dans-excel/
Insert/Name/Define/name a field, for example, directory
in Refers to, enter =READ.DOCUMENT(2) 2 being the read function
then in a cell of the workbook enter this formula
=MID(Directory;SEARCH("§";SUBSTITUTE(Directory;"\";"§";3))+1;9^9)
which will only give the name of the directory
however, you will need to save the file as choosing the .XLS or .XLSM extension
if you want to keep the .XLSX extension, you can get directly to the result with this lengthy formula
=MID(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);SEARCH("µ";SUBSTITUTE(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);"\";"µ";LEN(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2))-LEN(SUBSTITUTE(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);"\";""))))+1;100)
A+
Mike-31
I am responsible for what I say, not for what you understand...
There are old XL4 macro syntaxes like READ.CELL for the curious; I detailed some possibilities at this link
https://www.commentcamarche.net/applis-sites/bureautique/1521-compter-ou-additionner-des-cellules-colorisees-manuellement-dans-excel/
Insert/Name/Define/name a field, for example, directory
in Refers to, enter =READ.DOCUMENT(2) 2 being the read function
then in a cell of the workbook enter this formula
=MID(Directory;SEARCH("§";SUBSTITUTE(Directory;"\";"§";3))+1;9^9)
which will only give the name of the directory
however, you will need to save the file as choosing the .XLS or .XLSM extension
if you want to keep the .XLSX extension, you can get directly to the result with this lengthy formula
=MID(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);SEARCH("µ";SUBSTITUTE(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);"\";"µ";LEN(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2))-LEN(SUBSTITUTE(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-2);"\";""))))+1;100)
A+
Mike-31
I am responsible for what I say, not for what you understand...
I'm having trouble breaking down this formula!! Now I would like to display the name of the folder where my file is located, here "01_com"
Thank you
Now it's up to you to search ;-)
STXT cut, FIND should allow you to find the position of the "["