0

I am trying to view the name of the file that I am working on in Excel. I would like to use a formula to return the file name to a cell on the spreadsheet. For example, if the file was called workbook1_AD00234.xls, then I would want cell A1 to show AD00234 and cell B1 to show workbook1. How can I do this with a formula?

  • 1
    What is AD00234? And why is it split off? – Raystafarian Nov 5 '15 at 18:30
2

The CELL function can be used to get the filename of the current workbook. On a Mac, it returns a colon separated path to the directory containing the workbook, the workbook name in square brackets, then the current sheet name, so something like:

=CELL("filename",A1) -> My HD:Users:myuser:Desktop:[workbook1_AD00234.xls]Sheet1

for Sheet1 in a workbook called workbook1_AD00234.xls stored on my Desktop. Given that, you can extract parts of it:

       A
1  =CELL("filename",A1)
2  =MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)
3  =LEFT(A2,FIND("_",A2)-1)
4  =RIGHT(A2,LEN(A2)-FIND("_",A2))

resulting in:

       A
1  My HD:Users:myuser:Desktop:[workbook1_AD00234.xls]Sheet1
2  workbook1_AD00234.xls
3  workbook1
4  AD00234.xls

On Windows I suspect the path format is different, so you may need to modify the formula in A2, although if the path is different but the workbook name is still in square brackets, the one above should still work. A2 extracts everything from A1 between the first open square bracket and the first close square bracket after the first open square bracket. The formula in A3 extracts everything to the left of the first underscore from the filename in A2. The formula in A4 extracts everything to the right of the first underscore from the filename in A2.

  • Skip step 1 =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) also that first formula would be circular reference, wouldn't it? – Raystafarian Nov 5 '15 at 18:33
  • 1
    @Raystafarian Yes, if you wanted, you could make A3 and A4 the entire formula so they'd stand alone. However, those formulas get complex with lots of repeated subformalus, and I thought they'd be easier to understand by splitting things up like this. Once someone sees what's going on, they can rearrange things as needed. Also, CELL is kind of the essence of the answer, once you have that, it's all just text manipulation (which Excel is kind of bad at), so it's useful to have that as the starting point (in my opinion). – blm Nov 5 '15 at 18:37

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.