View original (French)

File name without displaying the full path

yomosaique -  
Mike-31 Posted messages 18405 Registration date   Status Contributor 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

2 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    Maybe like this

    =MID(CELL("filename";A1);FIND("[";CELL("filename";A1))+1;FIND("]";CELL("filename";A1))-FIND("[";CELL("filename";A1))-1)

    --
    Cheers
    Mike-31

    I am responsible for what I say, not for what you understand...
    4
    1. jee pee Posted messages 31889 Registration date   Status Moderator Last intervention   9 982
       
      Hello, and change the -1 at the end to -6 to remove the .xlsx.
      0
    2. yomosaique
       
      Super thanks!
      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
      0
      1. jee pee Posted messages 31889 Registration date   Status Moderator Last intervention   9 982 > yomosaique
         
        01_com non, outil_commercial

        Now it's up to you to search ;-)

        STXT cut, FIND should allow you to find the position of the "["
        0
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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...
    0