Excel filenames and worksheet names as a formula

=CELL(“filename”) can be used to return the full file path in square brackets plus the worksheet name, so you can manipulate this with the MID and FIND functions to just return the folder location or the filename.

 

filenameformulas2

 

Folder location: =MID(CELL(“filename”),1,FIND(“[“,CELL(“filename”))-1)

Filename: =MID(CELL(“filename”),FIND(“[“,CELL(“filename”))+1,FIND(“]”,CELL(“filename”))-FIND(“[“,CELL(“filename”))-1)

Full file location: =A2 & A5

 

Returning the Worksheet Name as a formula

There isn’t a specific function to return the worksheet name, but with a bit of creativity you can concoct one yourself using a combination of the CELL, FIND and MID functions.

 

CELL(“filename”)¬†is again useful by returning¬†the full file path in square brackets, followed by the worksheet name. So to cut it down to just the worksheet name you need to find the closing bracket ] and grab anything to its right, which will be the active worksheet name. The following formula achieves this:

 

=MID(CELL(“filename”,A3),FIND(“]”,CELL(“filename”,A3))+1,256)

 

NB: The significance of the 256 at the end, is that 256 is the maximum character length of a worksheet name.

Leave a Reply

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