I need to extract - in a cell - the last folder name where the active excel file is stored.

E.g. C:/Documents/Users/Accounting/May 2019/Expenses.xls

I want to extract/output the text "May 2019" in a specific cell by using a formula (not a macro).

Thank you in advance.

2 Answers

Solar Mike On Best Solutions

So, I went with using the CELL() function to get the full path and then cut out the last folder name.


=TRIM(MID(SUBSTITUTE(CELL("filename",A1),"/",REPT(" ",999)),4*999-998,999))

enter image description here

Edit, As Ron pointed out that was fixed for the number of folders... This works around that:


=TRIM(MID(SUBSTITUTE(CELL("filename",A1),"/",REPT(" ",999)),SUM(IF(CELL("filename",A1)<>"",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"/","")),0))*999-998,999))

enter image description here


Localization can mean ";" instead of "," and "\" instead of "/", so take care...

Community On
=ANNULLA.SPAZI(STRINGA.ESTRAI(SOSTITUISCI(CELLA("filename";A1);"\";RIPETI(" ";999));SOMMA(SE(CELLA("filename";A1)<>"";LUNGHEZZA(CELLA("filename";A1))-LUNGHEZZA(SOSTITUISCI(CELLA("filename";A1);"\";""));0))*999-998;999))

Localized Italian version of @SolarMike code's.