How To Get The Current Workbook Folder Path

Example

=LEFT(CELL("filename",C2),FIND("[",CELL("filename",C2))-2)

Generic Formula

=LEFT(CELL("filename",AnyCell),FIND("[",CELL("filename",AnyCell))-2)

  • filename” – This is a predefined Excel parameter input for the CELL function.
  • AnyCell – This is can be any cell in any sheet of the workbook.

What It Does

This formula will return the folder path where the current workbook is saved.

How It Works

CELL(“filename”,AnyCell) will return the full folder path, file name and sheet name of the referenced cell. For my workbook this was the following location.

C:\Users\John\Google Drive – Excel\Excel Website\Formulas\How To Get The Current Workbook Folder Path\[How To Get The Current Workbook Folder Path.xlsx]Sheet1

All we need to do is parse all the text up until the first square bracket [ seen above highlighted in yellow.

We will first need to find the location of [ using FIND(“[“,CELL(“filename”,AnyCell)). This will give us the location of the first occurrence of the [ character within our full location path. For my workbook this was 103.

This 103 value is the number of characters up to and including the [, but this includes a \ and [ character which we don’t want so we will need to subtract 2. We only want the left most 101 characters.

Now we can use the LEFT function to return the left most 101 characters. LEFT(CELL(“filename”,AnyCell),101) will then result in the following path.

C:\Users\John\Google Drive – Excel\Excel Website\Formulas\How To Get The Current Workbook Folder Path

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us on social media to stay up to date with the latest in Microsoft Excel!