Quickly Get Sheet Name

This post explains that how to get the sheet name only in your current worksheet in excel. How do I insert the current worksheet’s name into one cell quickly.

Get Sheet Name with Formula


If you want to quickly get current worksheet name only, then inert it into one cell. You can use a formula based on the MID function in combination with the FIND function and the Cell function.

You can type the following formula into the formula box of cell B1, then press enter key.

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255)

get sheet name1

Let’s see how this formula works:

=CELL(“filename”,B1)

The cell function will get the full file name and its path of your current worksheet. The result look like this: C:\ [book1.xlsm]sheet1

get sheet name2

=FIND(“]”,CELL(“filename”,B1))+1

The FIND function will get the first position of the character “]”, then passed it into the MID function to extract the sheet name.

get sheet name3

Get Sheet Name with VBA


You can also use an Excel VBA Macro to get all worksheet’s name and insert those sheet name into the different cells. Just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

get sheet name4

#5 back to the current worksheet, then run the above excel macro. Click Run button.

get sheet name5

#6 Lets see the result:

get sheet name6

Related Functions

  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar