WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

Capture the name of a file using a formula

7/29/2018

3 Comments

 

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)
Picture

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

Reply
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

Reply
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

Reply

Your comment will be posted after it is approved.


Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me