Example
Generic Formula
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. 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.
