MrExcel.com - Your One Stop for Excel Tips & Solutions

Worksheet Name in Cell

July 13, 2017 - by

Worksheet Name in Cell

Use a formula in Excel to put the worksheet name in a cell. Also in this episode: =CELL("filename",A1) returns the path and file name

If you want each report to have the name of the worksheet as a title, use

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)) & " Report"

The CELL() function in this case returns the full path\[File Name]SheetName. By looking for the right square bracket, you can figure out where the sheet name occurs.

Worksheet Name as Report title
Worksheet Name as Report title

Watch Video

  • Use a formula in Excel to put the worksheet name in a cell. Also in this episode: =CELL("filename",A1) returns the path and file name
  • Look for the right square bracket using FIND
  • Add 1 character to the result of FIND
  • Pass that to MID
  • Uservoice votes at http://mrx.cl/sheetname

Video Transcript

MrExcel podcast is sponsored by "MrExcel XL", a book with 40+ Excel tips, plus Excel cartoons, cocktails tweets and jokes.

Learn Excel from MrExcel podcast, episode 1982 - Worksheet Name in a Cell!

This podcast is part of a series where I'm podcasting this entire book, go ahead and click up there to subscribe to the MrExcel Excel playlist.

Alright, yesterday I showed you how to use fast worksheet copy, by CTRL-dragging, and, did you notice that the worksheet title up there was automatically changing? How did I do that? It is this crazy formula that we have to use, in order to get the worksheet name into a cell, and it all starts with this formula here, =CELL("filename"), cell of a filename returns the complete path, workbook name, and sheet name. Alright, so that gives us a lot more than we want. And then I have to find where that right square bracket is, so the FIND("]") tells me that that's in position 53, but the sheet name then must be one more than that, so, whatever the result of the find is +1, we start there, and then use the MID, from 54, and just go out, you know, however long you think. 20, 25, whatever you need to do, to get the longest possible sheet name. And then to put it all back together, there's the MID of the CELL, where do we start, we do the FIND of the ] within the cell, +1, go out 20, and then I concatenated the word "Report". Alright, so let's just do a little test here, if I call this "Summary", as soon as I press Enter, you see that, that changes.

I hate putting this in the book because, it's not something I can remember, I have to go back to page 13 in the book, every time I want to try to do this, and, in fact, just in July, someone named Anonymous, created an Excel.UserVoice.com idea, for SHEETNAME() and SHEETNAMES() ! This one's worth a vote, if you voted yesterday, go out and vote for this one today, I created a short link: mrx.cl/sheetname , that would be a nice thing to do. So we won't have to go through this crazy, crazy formula, every time we want a sheet name in a workbook.

Well, this month I'll be podcasting this entire book, it'll take the whole month or you can just save time, buy the whole book right now. It'd be a great cross-reference to all the things we talked about, in this month's podcast.

Recap for today's episode: We started using the CELL("filename") function, gets me the whole path workbook name and file name. Look for that ] using FIND, add one character to pass that whole thing to the MID. Let's just vote to have the Excel team fix this!

Alright, hey I want to thank everyone for stopping by, we'll see you next time for another netcast from MrExcel!

Title Photo: stux / pixabay