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?

Improve this question
1
  • 1
    What is AD00234? And why is it split off?
    – Raystafarian
    Nov 5, 2015 at 18:30

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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, 2015 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, 2015 at 18:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .