Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Perform case-sensitive Lookup in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel ROWS Function
  • How to get last row in numeric data in Excel
  • How to get address of named range in Excel

Data Analysis Examples

  • Excel Bar Chart
  • Conditional Formatting Color Scales Examples in Excel
  • Filter Data Based on Date in Excel
  • Chart Axes in Excel
  • Subtotal function in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation exists in list
  • Excel Data validation with conditional list
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between

How to get sheet name only in Excel

by

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this:

Worked Example:   How to Remove Leading Spaces in Excel
Sheet1

Formula

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

Explanation

How the formula works

The cell function is used to get the full file name and path:

CELL("filename",A1)

The result looks like this:

path[workbook.xlsm]sheetname

The full file name plus path and sheet is fed into the MID function, which is used to extract just the sheet name.

Worked Example:   Understanding Named Range, Named Constant, Name Manager in Excel Formulas

The starting position is calculated with FIND:

FIND("]",CELL("filename",A1))+1

The number of characters to extract is supplied as 255. In the Excel UI, you can’t name a worksheet longer than 31 characters, but the file format itself permits worksheet names up to 255 characters.

Worked Example:   How to Capitalize first letter in a sentence in Excel

Alternative with RIGHT

Several readers have mentioned an alternative like this with the RIGHT function:

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

A few more function calls, but a perfectly valid formula.

Post navigation

Previous Post:

How to create dynamic worksheet reference in Excel

Next Post:

Get workbook name and path without sheet in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains number in Excel
  • How to get last line in cell in Excel
  • How to convert numbers to text using TEXT function in Excel
  • Put names into proper case — Manipulating NAMES in Excel
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • Get fiscal year from date in Excel
  • Get week number from date in Excel
  • Dynamic date list in Excel
  • How to join date and text together in Excel
  • How to determine year is a leap year in Excel
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles