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

Lookup and Reference Examples

  • How to get address of named range in Excel
  • How to get last column number in range in Excel
  • How to create dynamic named range with OFFSET in Excel
  • How to get address of last cell in range in Excel
  • Convert text string to valid reference in Excel using Indirect function

Data Analysis Examples

  • Data Series in Excel
  • How to Use Solver Tool in Excel
  • How To Filter Data in Excel
  • How To Create Pareto Chart in Excel
  • How to Create Gantt Chart in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation require unique number
  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells

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:

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:   Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

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:   Convert text date dd/mm/yy to mm/dd/yy 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 Separate Text Strings in Excel
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract text between parentheses in Excel
  • How to convert numbers to text using TEXT function in Excel
  • Complete List of Excel Text Functions References and Examples
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal minutes to Excel time
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • Tax rate calculation with fixed base in Excel
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles