Friday, September 23, 2016

Get Parent Folder Name in Excel using Function

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.

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



No comments:

Post a Comment