Excel Text 3 of 4: Using SEARCH, MID & CELL to Display Sheet Name

Linking the worksheet name to a cell in the spreadsheet is easily accomplished using Excel’s CELL function. Once joined by the MID and SEARCH functions, you need only change the sheet’s name and the linked cell will update to match.

Click to view at full resolution
Click to view at full resolution

Displaying the Sheet Name in a Cell

Type (or copy and paste) the following formula into a cell

=MID(CELL(“filename”),SEARCH(“]”,CELL(“filename”))+1,100)

Note

  • The file must be saved; the CELL function will not return a value on an unsaved file.
  • Press F9 (if necessary) to update the cell after a renaming a sheet.
MID returns a portion of text based on starting position and number of characters. =MID(“Task Quickly”,2,5) returns “ask Q”; starting with the 2nd char, 5 charachters long.
CELL returns info about the cell, file or sheet. =CELL(“filename”) returns the path, filename and sheet.
E.g., C:\[Balance.xlsx]Sheet1
SEARCH returns  the number where the first occurrence of the search text is found, regardless of case.* =SEARCH(“A”,”Quantity”) returns 3;  ‘A’ is the 3rd character.

*The FIND function works similar to SEARCH but is case-specific.

To learn more about each function and understand why this works, watch this ~2½ minute video:

Cheers!

hɔuᴉnb

Additional reading:

Advertisements

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow.

3 thoughts on “Excel Text 3 of 4: Using SEARCH, MID & CELL to Display Sheet Name”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s