Entering current folder into a cell

Hi.

I have been looking for a way to enter the folder where the excel file is located into a cell.
I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For example:

C:\docs\folder\data\Class D4\John Wayne\Profile.xlsx

Would enter "John Wayne" into cell. The cell is namely the "name" cell.

I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).

With thanks for any help.

Hi Nimril

Welcome to the forum

Does the attached do what you want? It uses VBA and will work on any file (Press Ctrl+shift+Q) so long as the Name Find file is open.

To view the code, press Alt+F8 / Step into

Regards
Alastair

Thank you Alastair.

After trying several things I realised that every solution would use a Macro of some type. I left the post on since I was still curious on how to do it since I was going around in circles while trying.

However since the macros are disabled by default on Excel throughout the school I realised that no mater how simple I made it would still mean people would encounter problems when tying to use it.

I opted for a template that just fills the other details from the 1st worksheet, cutting down the amount they have to fill in, yet simple enough to not need any user interaction.

Richard

Hi Richard

If you could guarantee that (in your example) John Wayne would always be after the 5th backslash, you might be able to use a complicated "substitute" based formula?

Regards
Alastair