Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Display folder name only with formula where is placed opened Workbook

How to display the path to the sub folder (directory) in the cell, in which there is opened Excel workbook with formula

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 for the path to the Excel Workbook

How to return 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 or the name of the folder 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 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 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.

Display 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 file. 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 function FIND, 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 functions FIND 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 function MID. 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 sub folder)
=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 only the name of subfolder in which there is opened Excel workbook but not the entire path
The final formulas in the cell "A26" is as follows (formulas is located 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);"\";""));"")

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 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.

Attention! My operating system uses a semicolon as a separator of arguments in Excel formulas. Please note, you may need to use a comma as the separator formula.