What Excel formula returns the sheet name?

Question

I have searched the excel function documentation and general MSDN search but have been unable to find a way to return the sheet name without VBA.

Is there a way to get the sheet name in an excel formula without needing to resort to VBA?

Travis · Answer 1 · 2015-02-01T18:38:29.447

22

Not very good with excel, but I found these here

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

and A1 can be any non-error cell in the sheet.
For the full path and name of the sheet, use

=CELL("filename",A1)

edited Feb 01 '15 at 18:38

answered Feb 01 '15 at 17:31

Travis

1,274
1
16
33

It doesn't work properly if any folder in the path of this file contains character ] in its name. – mielk Apr 19 '19 at 01:34
3

I thought I was clever by omitting the `,A1`s, and changing `256` to `255`, but that has the bug that it doesn't work if one recalculates the workbook from a different sheet: in that case its value is always the name of the active sheet. – cp.engr Jul 15 '19 at 14:02
Very useful, thanks a lot. Just a note, you have to save the file somewhere for this to work (it can't be "Book 1" or whatever). – Alex S Mar 24 '20 at 20:01
@AlexS Thanks for the feedback! It seems the resource I linked originally has changed drastically since I posted this- thank you for confirming it still functions. – Travis Mar 25 '20 at 21:20

pgSystemTester · Answer 2 · 2021-07-09T15:45:18.817

Here's a reasonably short one that has a couple added benefits:

Does a reverse lookup (most other answers go wrong direction) by using the often ignored REPT function.
A1 seems like a poor choice as there's a considerably higher chance it errors compared to... $FZZ$999999.
Don't forget to absolute. Copying and pasting some of the other examples could error due to referential changes.

The ? is intentional as that shouldn't be in the file path.

=SUBSTITUTE(RIGHT(SUBSTITUTE(CELL("Filename",$FZZ$999999), 
"]",REPT("?", 999)), 999),"?","")

score 4 · Answer 3 · edited Jun 12 '18 at 08:49

4

The below will isolate the sheet name:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

edited Jun 12 '18 at 08:49

eli-k

10,898
11
40
44

answered Aug 08 '16 at 03:52

ADrunkenMan

41
2

2

This doesn't work if one recalculates the workbook from a different sheet: in that case its value is always the name of the active sheet. – feetwet Aug 20 '18 at 19:18
1

@feetwet had the same problem, reason is that `CELL("filename")` misses the optional, but in this case necessary reference parameter. Using cell with a reference to any cell in the workbook, "fixes" the behavior as expected: `CELL("filename"; A1)`. – Kim Jun 01 '21 at 08:16

score 4 · Answer 4 · edited Oct 17 '18 at 11:43

4

For recent versions of Excel, the formula syntax is:

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)

edited Oct 17 '18 at 11:43

Wizhi

6,424
4
25
47

answered Oct 17 '18 at 10:59

Orbit

212
1
7

score 4 · Answer 5 · answered Apr 19 '19 at 01:47

None of the formulas given in other answers supports the case if there is character ] in filepath.

The formula below is more complex, but it can handle such cases properly:

 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1),FIND("?",SUBSTITUTE(CELL("filename",A1),"\","?",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\","")))))+1,LEN(CELL("filename",A1)))

score 1 · Answer 6 · edited Jun 12 '18 at 08:50

1

I had a module already open so I made a custom function:

Public Function Sheetname (ByRef acell as Range) as string
Sheetname = acell.Parent.Name 
End Function

edited Jun 12 '18 at 08:50

eli-k

10,898
11
40
44

answered Dec 21 '17 at 15:00

Branston Crumble

11
1

1

Looks like your answer is incomplete. Please demonstrate how it answers the original question. Also, use {} button to format as code. – RaphaMex Dec 21 '17 at 15:11
2

This is not answering OP's question, as he asked specifically "Is there a way to get the sheet name in an excel formula *without needing to resort to VBA?*", yet this is a VBA answer? – P-L Dec 04 '18 at 15:18
I would change this to read `Sheetname = acell.Worksheet.Name` . – pgSystemTester Jul 09 '21 at 15:47

score 0 · Answer 7 · answered Jun 14 '19 at 02:27

Had a square bracket ']' in the file name, so needed to modify the above formula as per the following to find the last occurrence of the square bracket. Verified that this works for 0,1 or more square brackets in the file name / path.

=RIGHT(CELL("filename"),LEN(CELL("filename")) - FIND("]]]",SUBSTITUTE(CELL("filename"),"]","]]]",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"]","")))))

Replaces all square brackets using the substitute function, then compares the length of the result with the length of the file name to identify the number of square brackets in the file name.
Uses the number of occurrences of square brackets to substitute the last square bracket with 3 sequential square brackets ']]]'
Uses the Find function to identify the location of the 3 square brackets in the string
Subtracts the location of the 3 square brackets from the length of the full path
Uses the result to get the right most characters (being the sheet name)

Previous comment above about saving the workbook first is also a key, as you'll otherwise receive the #Value! result.

score 0 · Answer 8 · answered Dec 26 '20 at 21:19

The below works for me and is simpler (to me at least) than other solutions, while still handling a square bracket in the file name:

=MID(CELL("filename", A1),6+SEARCH(".xlsx]",CELL("filename", A1)),32)

This assumes it is an "xlsx" file and that the filename will not contain ".xlsx]" in addition to the xlsx suffix, which in my case is an assumption that is safe enough to make.

If you wanted to handle both ".xlsx" and ".xls" file names, you could use:

=MID(SUBSTITUTE(CELL("filename", A1),".xls]",".xlsx]"),6+SEARCH(".xlsx]",SUBSTITUTE(CELL("filename", A1),".xls]",".xlsx]")),32)

score -2 · Answer 9 · answered Feb 08 '17 at 21:42

I'm pretty sure you could have Googled this. I just did, and here is the very first thing that came up for me.

In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how;

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;

=CELL("filename",A1)

The only catch is that you have to save the file for this to work!

What Excel formula returns the sheet name?

9 Answers9

Linked