File Name, Sheet Name: Easily Insert File Data in Excel!

There are many cases, in which you want to show the sheet name or file name in an Excel cell. Don’t worry, you don’t have to use VBA and create a macro. Excel provides a (rough) formula for returning all the information you need. The bad news: In most cases it takes some complex modifications. The good news: Usually you can just copy and paste the formula you need.

Introduction: Get all the names you need

Fortunately, you don’t have to use VBA. There are formulas for each variation of the sheet name, file name and path. The main part of the formula is the =CELL() formula.

The CELL formula looks something like this: =CELL(“filename”,A1). That means that the CELL formula we have to use has two parts:

  1. The first part just says “filename”. It is important to add the quotation marks.
  2. The second part is optional and can link to any cell or workbook. If you leave it blank the current cell and workbook will be used.

The basic version of the CELL formula (=CELL(“filename”,A1) ) returns the full path, file name and sheet name. You can use this formula and cut it into the parts you need. Therefore you use string formulas like LEFT, LEN and so on. We won’t go into detail now as those formulas are highly specific and complex but rather provide the formulas for you to copy.

Note: We’ve published an article about three methods of how to get the sheet name. Maybe this one is interesting for you as well.

The formulas for sheet name, file name, path etc.

The formulas for inserting file names, sheet names or the path (with and without the extension) are very different. This table gives you an overview. If you want to copy the formulas, you can find them underneath again.

file name, sheet name, formula, extension, copy, path
Overview: All the formulas for getting a file name, sheet name or path in Excel

Copy these formulas for the current cell or sheet:

  • Worksheet name (example: “Formulas”):
    =RIGHT(CELL(“filename”),LEN(CELL(“filename”))-SEARCH(“]”,CELL(“filename”, $A$2)))
  • Workbook name (example: “Formulas_SheetName_FileName”):
    =TRIM(LEFT(SUBSTITUTE(MID(CELL(“filename”),FIND(“[“,CELL(“filename”))+1,255),”.xl”,REPT(” “,255)),255))
  • Workbook name + extension (example: “Formulas_SheetName_FileName.xlsx”):
    =MID(CELL(“filename”),SEARCH(“[“,CELL(“filename”))+1,SEARCH(“]”,CELL(“filename”))-SEARCH(“[“,CELL(“filename”))-1)
  • File path (example: “C:\Desktop\”):
    =LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)
  • File path + filename (example: “C:\Desktop\Formulas_SheetName_FileName”):
    =LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)&TRIM(LEFT(SUBSTITUTE(MID(CELL(“filename”),FIND(“[“,CELL(“filename”))+1,255),”.xl”,REPT(” “,255)),255)) 
  • File path + filename + extension (example: “C:\Desktop\Formulas_SheetName_FileName.xlsx”):
    =SUBSTITUTE(LEFT(CELL(“filename”),FIND(“]”,CELL(“filename”))-1),”[“,””)
  • File path + filename + extension + worksheet name (example: “C:\Desktop\[Formulas_SheetName_FileName]Formulas”):
    =CELL(“filename”)
  • Folder name (example: “Desktop”):
    =TRIM(RIGHT(SUBSTITUTE(LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”),1)-2),”\”,REPT(” “,100)),100))

Copy these formulas for any linked cell or sheet:

If you want to get the file name, sheet name or path from another cell or workbook, you can use one of the following formulas. Instead of “A1” you insert your cell reference.

  • Worksheet name (example: “Formulas”):
    =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-SEARCH(“]”,CELL(“filename”, A1)))
  • Workbook name (example: “Formulas_SheetName_FileName”):
    =TRIM(LEFT(SUBSTITUTE(MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,255),”.xl”,REPT(” “,255)),255))
  • Workbook name + extension (example: “Formulas_SheetName_FileName.xlsx”):
    =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-1)
  • File path (example: “C:\Desktop\”):
    =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1)
  • File path + filename (example: “C:\Desktop\Formulas_SheetName_FileName”):
    =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1)&TRIM(LEFT(SUBSTITUTE(MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,255),”.xl”,REPT(” “,255)),255))
  • File path + filename + extension (example: “C:\Desktop\Formulas_SheetName_FileName.xlsx”):
    =SUBSTITUTE(LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””)
  • File path + filename + extension + worksheet name (example: “C:\Desktop\[Formulas_SheetName_FileName]Formulas”):
    =CELL(“filename”,A1)
  • Folder name (example: “Desktop”):
    =TRIM(RIGHT(SUBSTITUTE(LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-2),”\”,REPT(” “,100)),100))

Please note:

  • The formulas are different for each language and regional settings. So you have to make sure to adapt it to your Excel version. Please check out the download below, it’s probably the easiest option for adapting the formulas to your settings.
  • In order to insert the filename or path, you have to save your workbook first. It doesn’t work with a newly created Excel file.

Download an Excel file with all the formulas

download, excel, formulas, cell, file name, sheet name, path
Download the formulas within this Excel file so that it adapts to your language and regional settings automatically

In order to avoid any problems with language and regional settings, you can download all the above formulas with an Excel file.

Just open it and copy the complete cell to your desired location. The download starts instantly (no sign-up or any other annoyances…).

  • Download link: Click here.
  • File name: Formulas_SheetName_FileName.xlsx
  • Size: 35 KB
  • File type: XLSX

Anything missing? Suggestions for improvements? Please let us know and leave a comment.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

4 comments

  1. I get my filename and path by inserting

    =cell(“filename”) and it put it right where I want it (next to bottom row in spreadsheet so it shows when I print) . However, as I am working with several spreadsheets, the filename changes to the last spreadsheet worked with, so I constantly need to re-insert the correct filename. Very frustrating.

    Any solutions to stop my filename from changing. It should always reflect the corret filename for the spreadsheet I am working on, once I have inserted after the first time.

    Kindest,

    Bob

  2. I’ve made a reference in one cell to another one which is in another file (this file is located in another place).
    the Problem is ( sometimes it refer it as in harddisk H:, othertimes as //EVA/EAR) so it couldn’t make the update of the data (and it doesn’t complain that he didn’t find the path)

    Vlookup(TV$2;’\Eva\EAR\Daten\……….
    Vlookup(TV$2;’H:\Daten…….

Leave a comment

Your email address will not be published. Required fields are marked *