Capture the name of a file using a formula

7/29/2018

In my past few blog posts, I demonstrated how to transfer data from multiple files into a master spreadsheet. Another useful input to transfer into the master file is the name of each input file.

In order to transfer this information into the master file we first need to capture the name of each file within the original input file. Here is a formula which performs this task.

=MID(CELL("Filename"),SEARCH("[",CELL("Filename"))+1,SEARCH("]",CELL("Filename"))-SEARCH("[",CELL("Filename"))-1)

3 Comments

Brian Canes

7/30/2018 11:49:02 pm

Shorter and only one reference to Cell("Filename")
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(CELL(""Filename""),INFO(""Directory"")&""["",""""),""]"",REPT("" "",256)),256))
Note that the current directory needs to be the folder that contains the file.
Regards
Brian

Mehran Vahedi link

7/31/2018 11:12:22 am

Hi Brian,

Thank you very much for sharing this formula. I wasn't able to make this work.

My understanding is that the directory name (i.e. the folder that contains the file) needs to be manually inputted into the formula (correct me if I'm wrong), but doesn't this mean more manual work and won't it cause issues in scenarios when We have multiple folders or files are frequently moved from one folder to another or emailed to different recipients where they store the spreadsheet in a folder of their choice.

Please share your thoughts.

Many thanks,

Mehran Vahedi

Brian Canes

7/31/2018 11:31:10 am

Works for me. No manual entry. Do a Save or SaveAs then the current directory will be the folder containing the file.
Regards
Brian

Your comment will be posted after it is approved.

Capture the name of a file using a formula

Leave a Reply.

Categories

Archives