Excel tutorials

Return Folder Name Only With Formula Where Is Placed Opened Workbook

How to Display the Path to the Subfolder (Directory) in the Cell

I believe that you know the formula by means of which you can get a (display) or path to the folder in which there is opened workbook (Excel file).

If you are not familiar with the formula, here it is. The formula below shows the full path to the folder in which there is opened file.

=LEFT(CELL("filename";A2);FIND("[";CELL("filename";A2);1)-1)

The result is as shown below.

The formula return the path to the Excel Workbook

How to Display the the Name of the current Folder (Directory) in a cell in which there is opened Excel Workbook

If you want to display the Name Folder which containing the opened Excel file (Excel Workbook) then use this formula below (formula is written in a single command line). See the result of the formula in cell 'A7' in the picture above.

=IFERROR(TRIM(SUBSTITUTE(MID(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1);FIND(CHAR(1);SUBSTITUTE(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1);"\";CHAR(1);(LEN(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1))-LEN(SUBSTITUTE(LOWER(LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1);1)-1));"\";"")))/1-1))+1;100);"\";""));"")

If you want to know how this formula works (of which all parts are made this formula), then you should know the arguments for certain Excel functions that are used. Of course, you can use Evaluate Formula or the F9 key on the keyboard.

How does it work formula to display the name of the sub folder where is placed opened file

This long formula above to display the name of the folder in which it is located opened Workbook. This formula consists of multiple nested functions (formulas). To you could understand how the formula works, you need to use "Evaluate formula" or the F9 key on the keyboard, which makes calculation of a selected function argument.

Therefore, it is necessary to know the arguments of certain functions that we want to use. I will later in this Excel tutorial try to show how we can create a long Excel formula of multiple nested Excel functions. Look at the picture below and follow the picture during viewing this tutorial. You note that the steps at the end showing the desired result.

Get the name of the subfolder in which there is opened Excel file with formula.

In the cell 'A2' is a formula that displays the full path to the sub-directory in which there is opened Excel Workbook. You look at the first formula at the beginning of this tutorial.

In the cell 'A7' is a formula that counts the number of character "\" in the cell 'A2' and of course minus one character because we want to count all the characters to second last character. If we know the formula for counting the total number of specific character, it is easy to calculate the sum of character minus one character. The result of this formula is the number '8'. This means that the in total result of the formula is 9 characters "\". Of course minus one character, and the result is the eighth.

Formula in the 'A8' cell is: (9-1=8)

=((LEN(A2)-LEN(SUBSTITUTE(LOWER(A2);"\";"")))/1)-1

In the cell 'A11' is a formula created using Excel FIND function, which as a result of returns the position before the last character. If we know that the function =LEN(A2) returns the total number of characters in the cell, then simply return second last position of the specific character. You you may notice in the formula below SUBSTITUTE function, has the last argument "instance_num" which in this case data from the cell 'A8'. If you are selecting an argument "within_text" Excel FIND functions and use the F9 key then you will notice that this function returns the path to the sub directory in which there is opened file (Workbook). The result of the formula below the value 74. This means that before the last character "\" is on the 74 place of the total number of characters.

Formula in the 'A11' cell is:

=FIND(CHAR(1);SUBSTITUTE(A2;"\";CHAR(1);A8))

In the cell 'A14' is a formula created using Excel MID functionfunction. This formula returns the name of the subfolder in which there is an open Excel workbook. You can observe that the result also shows the last character "\". He will be able to remove it later. This formula uses the data cell 'A2' and 'A11'. Value or the number "100" within the function, the last argument Excel functions MID. This number you can modify as desired and it depends on the maximum number of expected characters, named sub folder in which there is opened Excel workbook.

Formula in the 'A14' cell is:

=MID(text;start_num;num_chars)=MID(A2;A11+1;100)

In the cell 'A17' is a formula in the which we nested the previous formula. Thus, in the formula below we have a nested formula in cell 'A11' in the cell 'A14'. Association with other nested formula would look like below.

=MID(A2; FIND(CHAR(1);SUBSTITUTE(A2;"\";CHAR(1);formula_from_A8_cell))+1;100)
=MID(A2;formula_from_A11_cell+1;100)

The formula in the 'A17' cell (If, +1 we replace it with a zero then we get a specific character at the beginning of in the name of a subfolder).

=MID(A2;FIND(CHAR(1);SUBSTITUTE(A2;"\";CHAR(1);(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2);"\";"")))/1-1))+1;100)

In the cell 'A20' is a formula using Excel TRIM function, which uses the result of cells 'A17' and removes the last character "\".

Formula in the 'A20' cell is:

=TRIM(text)
=TRIM(SUBSTITUTE(A17;"\";""))

If instead of the cell address "A17" in the formula above, we nested the formula from cell "A17" then our formula in the cell 'A23' looks like below.

Formula in the 'A20' cell is:

=TRIM(SUBSTITUTE(MID(A2;FIND(CHAR(1);SUBSTITUTE(A2;"\";CHAR(1);(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2);"\";"")))/1-1))+1;100);"\";""))

And at the end of our formula, which returns the desired the result, after all nested formulas in the cell 'A26' looks like below. You notice that I am in the previous formula above, instead of 'A2' address cell nestled formula at the beginning of this tutorial, which returns the full path to the sub-folder.

How to display the name of subfolder only in which there is opened Excel workbook but not the entire path.

The final formulas in the cell "A26" is as follows (formula is placed in one command line).

=IFERROR(TRIM(SUBSTITUTE(MID(LEFT(CELL("filename";A2);FIND("[";CELL("filename";A2);1)-1);FIND(CHAR(1);SUBSTITUTE(LEFT(CELL("filename";A2);FIND("[";CELL("filename";A2);1)-1);"\";CHAR(1);(LEN(LEFT(CELL("filename";A2);FIND("[";CELL("filename";A2);1)-1))-LEN(SUBSTITUTE(LOWER(LEFT(CELL("filename";A2);FIND("[";CELL("filename";A2);1)-1));"\";"")))/1-1))+1;100);"\";""));"")

Excel IFERROR function serves us to remove any errors (but not necessary).

=IFERROR(ourFormula;"")

I hope you understand what I wanted to show in the this Excel example tutorial. So how to display the name of the subfolder (sub-directory) in which there is opened Excel workbook. If you copy this formula in the multiple Excel spreadsheets, regardless of the place where they are located, the formula will always properly return the correct name of the sub folder in which there is currently opened Excel workbook.

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.