One of my friend was working on a project, where he was required just the name of the parent folder for the current workbook file, not the complete path and he wanted it to be derived using a regular Excel formula, not a macro or user-defined function.
Here is the function which I have created for him. However, the function is bit long and complicated to understand. I have tried to make it more concise.
Above function is a combination of CHAR( ), MID( ), FIND( ), SUBSTITUTE( ), CELL( ), and LEN( ) functions.
Here is the function which I have created for him. However, the function is bit long and complicated to understand. I have tried to make it more concise.
=MID(CELL("filename"),FIND(CHAR(1),SUBSTITUTE(CELL("filename"),"\",CHAR(1),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))-1))+1,FIND("[",CELL("filename"))-2-FIND(CHAR(1),SUBSTITUTE(CELL("filename"),"\",CHAR(1),LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))-1)))
Above function is a combination of CHAR( ), MID( ), FIND( ), SUBSTITUTE( ), CELL( ), and LEN( ) functions.
