In everyday work with Microsoft Excel, situations often arise when it would be good to display either the full path to the current book (from the drive letter to the extension) or the path to the current folder where our book is stored in a specific cell. This can be done in several ways.
Method 1. Manually
Go to File – Details and click the button at the top copy path (File — Info — Copy path). After that, the full path to the current book will be in our buffer, from where it can be inserted anywhere in the standard way (for example, using the keyboard shortcut Ctrl+V).
Cheap and cheerful, but if you move the file to another folder, you have to do it all over again. For automatic updating, it is better to use formulas.
Method 2. CELL function
Excel has had a built-in function for a very long time. CELL (CELL), which can display a bunch of different useful information:
If used with an argument “name file” («filename»), then we get almost what we need:
It remains only to trim the tail after the closing square bracket using the functions LEVSIMV (LEFT) и TO FIND (FIND) and remove the opening square bracket with the function SUBSTITUTE (SUBSTITUTE):
Method 3. New LET function
As you can easily see, the same fragment with the CELL function is repeated twice in the previous formula. To shorten and simplify this construction a bit, you can use the new LET wrapper function:
The first argument to this function will be the name of the variable (any, for example, s), the second is the value of this variable (our CELL function), the third is the resulting formula involving the created variable.
- A detailed description of the capabilities of the CELL function in Microsoft Excel
- New FILTER, SORT, and UNIQ functions in the latest versions of Excel
- Creating links between worksheets and workbooks using the HYPERLINK function