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:
- The first part just says “filename”. It is important to add the quotation marks.
- 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.
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
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.