In this article, the Excel formula to insert the Name of the Worksheet is explained in detail.
Here is Step 1: Return the File Path, File Name and Sheet Name using CELL Function
CELL Function in Excel can used to return the information about a cell in an Excel Worksheet. The following formula will return the worksheet name, along with the file name and file path.
=CELL("Filename,A1")
Here, Test is the Sheet Name, Formula to Extract Sheet Names.xlsx is the File Name and F:\Excel Trick is the Folder Path
Step 2: Locate the position of ‘]’
FIND function can be used to locate the position of ‘]‘ in the filename returned by CELL function.
=FIND("]",CELL("filename"))+1
The formula has returned the number 53 which is the position of ] in the file name returned by CELL function.
Using the position of ‘]’ inside MID function, we can extract the sheet name
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,31)
Formula returned the Sheet Name Test.
Also read…